How to typecast an integer into a timestamp?

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 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

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

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 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