Thread: Timestamp

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

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
>

Ben <bench@silentmedia.com> writes:
> 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.

BTW: as of 7.3 the available casts are directly documented by the
pg_cast system catalog.  Development sources (7.4-to-be) have a \dC
command to display pg_cast's contents conveniently, but in 7.3 you can
do it the hard way:

    select castsource::regtype, casttarget::regtype from pg_cast;

In prior versions you could look in pg_proc for conversion functions,
but this did not tell you about binary-equivalence casts.

            regards, tom lane