Re: How to typecast an integer into a timestamp?

On Friday, January 27, 2012 7:44:55 am bboett(at)free(dot)fr wrote:
> Hello!
>
> again quite a stupid problem i regularly run into....
> and that i still haven't solved yet...
>
> again i used a type timestamp to keep a track of modification time, and
> again it gets stupid and confusing.....
>
> first of all the errors are labeled as timestamp without timezone, i only
> specified timestamp....
>
> the data was created as a timestamp with php-mktime, but when sending to
> the database postgres complains that its an int, and when i try to
> typecast it, (with the ::timestamp appendix to the value), that its not
> possible to convert an int to a timestamp (without timezone) .....
>
> so as usual i would discard the timezone datatype and alter the table to
> use integer instead, but this time i am wondering, since this datatype is
> present, there's surely a way to use it properly? but how?
>
> please enlighten me!

Did some digging. php-mktime returns the Unix epoch (seconds since January 1
1970 00:00:00 GMT)

Postgres has a function(to_timestamp) that will convert that to a timestamp:

http://www.postgresql.org/docs/9.0/interactive/functions-formatting.html

to_timestamp(double precision) timestamp with time zone convert Unix
epoch to time stamp to_timestamp(1284352323)

So something like the below in your query should work:

to_timestamp(int_returned_from_php)

>
> ciao
> Bruno

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com