Thread: How to typecast an integer into a timestamp?
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 intto 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! ciao Bruno
On Friday, January 27, 2012 7:44:55 am bboett@free.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.... http://www.postgresql.org/docs/9.0/interactive/datatype-datetime.html " Note: The SQL standard requires that writing just timestamp be equivalent to timestamp without time zone, and PostgreSQL honors that behavior. (Releases prior to 7.3 treated it as timestamp with time zone.) " > > 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) ..... Alter the field to be timestamp with time zone and see if that helps. FYI if you want to cast to timestamp with time zone, use ::timestamptz > > 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! > > ciao > Bruno -- Adrian Klaver adrian.klaver@gmail.com
On 1/27/2012 9:44 AM, bboett@free.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
intto 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!
>
> ciao
> Bruno
>
The problem is that php mktime returns an integer. Not a date/time.
mktime returns the number of seconds since Jan 1 1970.
The best answer is to not use mktime. Find a php function that returns
a formatted string like strftime('%Y.%m.%d').
-Andy
On Friday, January 27, 2012 7:44:55 am bboett@free.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.klaver@gmail.com
On Fri, Jan 27, 2012 at 08:17:37AM -0800, Adrian Klaver wrote: > Did some digging. php-mktime returns the Unix epoch (seconds since January 1 > 1970 00:00:00 GMT) indeed, didn't get it that postgres timestamp wasn't the same.... > 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) very neat that does it! thanks a lot everybody! ciao Bruno -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
On Saturday, January 28, 2012 1:43:43 am Bruno Boettcher wrote: > On Fri, Jan 27, 2012 at 08:17:37AM -0800, Adrian Klaver wrote: > > Did some digging. php-mktime returns the Unix epoch (seconds since > > January 1 1970 00:00:00 GMT) > > indeed, didn't get it that postgres timestamp wasn't the same.... Well internally they are stored that way. You just have to input the values as some sort of time/date/timestamp string. For all the details see here: http://www.postgresql.org/docs/9.0/interactive/datatype-datetime.html#DATATYPE-DATETIME-INPUT > > ciao > Bruno -- Adrian Klaver adrian.klaver@gmail.com
On Fri, Jan 27, 2012 at 08:17:37AM -0800, Adrian Klaver wrote: > Did some digging. php-mktime returns the Unix epoch (seconds since January 1 > 1970 00:00:00 GMT) indeed, didn't get it that postgres timestamp wasn't the same.... > 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) very neat that does it! thanks a lot everybody! ciao Bruno -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
On 2012-01-27, bboett@free.fr <bboett@free.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..... oops! (when recording events timestamp with timezone is usually best) > first of all the errors are labeled as timestamp without timezone, i > only specified timestamp.... it's the same thing since 8.1 > 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? just use a string in this format "YYYY-MM-DD HH:MM:SS.sssssss +NN:NN" -- ⚂⚃ 100% natural