Обсуждение: type casting reference?
hi. i need to cast from timestamp to seconds (in the form of integer). i have a table with a column: expiretime timestamp i have tried the following, it gives errors as below: SELECT expiretime::float4 AS timeleft FROM table WHERE something='1234'; ERROR: cannot cast type timestamp without time zone to real SELECT expiretime::text::float4 AS timeleft FROM table WHERE something='1234'; ERROR: invalid input syntax for type real: "2005-06-12 21:21:24.368082" postgresql 7.4.1 (i know, old) so, my question is simply, where in the docs can i find how i should perform the cast? it would help a lot if there was a table showing which types can be cased into which, and possibly an example or two for situations like this, casting from timestamp (to timestamp worked right away). /stig
stig erikson wrote: > hi. > i need to cast from timestamp to seconds (in the form of integer). > > i have a table with a column: > expiretime timestamp > > i have tried the following, it gives errors as below: > SELECT expiretime::float4 AS timeleft FROM table WHERE something='1234'; > ERROR: cannot cast type timestamp without time zone to real > > SELECT expiretime::text::float4 AS timeleft FROM table WHERE > something='1234'; > ERROR: invalid input syntax for type real: "2005-06-12 21:21:24.368082" > > > postgresql 7.4.1 (i know, old) > > so, my question is simply, where in the docs can i find how i should > perform the cast? > it would help a lot if there was a table showing which types can be > cased into which, and possibly an example or two for situations like > this, casting from timestamp (to timestamp worked right away). > > /stig solved my problem this way: UPDATE table SET expiretime= cast( ('now'::text)::timestamp+'30 minutes'::interval AS timestamp) WHERE sessionID='0';
On Jun 13, 2005, at 4:39 AM, stig erikson wrote: > hi. > i need to cast from timestamp to seconds (in the form of integer). > > i have a table with a column: > expiretime timestamp > > i have tried the following, it gives errors as below: > SELECT expiretime::float4 AS timeleft FROM table WHERE > something='1234'; > ERROR: cannot cast type timestamp without time zone to real > > SELECT expiretime::text::float4 AS timeleft FROM table WHERE > something='1234'; > ERROR: invalid input syntax for type real: "2005-06-12 > 21:21:24.368082" Are you looking for the timestamp expressed as seconds from the Unix epoch? If so, look at extract(epoch from expiretime). http://www.postgresql.org/docs/7.4/interactive/functions- datetime.html#FUNCTIONS-DATETIME-EXTRACT Michael Glaesemann grzm myrealbox com