On Friday, January 27, 2012 7:44:55 am bboett@xxxxxxx 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.klaver@xxxxxxxxx -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
