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.
Show quoted text
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
typecastsThe 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