Re: [GENERAL] How to typecast an integer into a timestamp?
On 2012-01-27, bbo...@free.fr bbo...@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 -MM-DD HH:MM:SS.sss +NN:NN -- ⚂⚃ 100% natural -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to typecast an integer into a timestamp?
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 zoneconvert 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
Re: [GENERAL] How to typecast an integer into a timestamp?
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.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 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 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! 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
Re: [GENERAL] How to typecast an integer into a timestamp?
On Friday, January 27, 2012 7:44:55 am bbo...@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.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to typecast an integer into a timestamp?
On 1/27/2012 9:44 AM, bbo...@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!
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to typecast an integer into a timestamp?
On Friday, January 27, 2012 7:44:55 am bbo...@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 zoneconvert 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.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
