You want stuff like last_login::int4::abstime
This should be a FAQ, because I also spent forever in frustration until
somebody helped me out with the above, which I've since passed on to
many people.
On Mon, 2003-01-06 at 19:32, Boget, Chris wrote:
> Though there are *alot* of things to like about PG, there
> are some times when I really want to throw in the towel on
> in out of sheer frustration. I think if the documentation
> were done (alot) better, it would go a long way to cut down
> on alot of mine (and I'm sure others') frustration.
> Sorry for the rant. Just been banging my head up against
> the wall with this for the last 4 hours.
>
> I'm overhauling my logins table so I renamed the old table
> to logins old. In logins_old, the signup_date, last_login
> and last_updated are all bigint columns storing a unix time
> stamp. In my (new) logins table, I'm going to be storing it
> as type timestamp. In trying to get the old data into the
> new table, I've tried to discover how I can convert the unix
> timestamp to the correct format. I've done about 1000
> permutations on the query below:
>
>
> INSERT INTO logins
> SELECT name, name, email, pass, country,
> abstime( signup_date )::timestamp,
> abstime( last_login )::timestamp,
> abstime( last_updated )::timestamp,
> record_num FROM logins_old;
>
> I've tried abstime, to_char and a few other functions to
> try to convert the data. The error I get on the above is:
>
> ERROR: Function abstime(bigint) does not exist
> Unable to identify a function that satisfies the
> given argument types You may need to add explicit
> typecasts
>
> The above ::timestamp is just the last attempt I tried at
> typecasting out of the probably 50 other attempts. I've
> also tried this:
>
> INSERT INTO logins
> SELECT name, name, email, pass, country,
> abstime( cast( cast( signup_date::bigint as text ) as int8 )),
> abstime( cast( cast( last_login::bigint as text ) as int8 )),
> abstime( cast( cast( last_updated::bigint as text ) as int8 )),
> record_num FROM logins_old;
>
> and got the same error. Umm, if the cast worked, it shouldn't
> be yelling about abstime(bigint) yet it is.
>
> What am I doing wrong and where in the documentation could I
> have gone (or did I miss) that would have explained how this
> could be done?
>
> Any help would be _greatly_ appreciated!
>
> Chris
>