Thread: How to typecast an integer into a timestamp?

How to typecast an integer into a timestamp?

From
bboett@free.fr
Date:
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

Re: How to typecast an integer into a timestamp?

From
Adrian Klaver
Date:
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

Re: How to typecast an integer into a timestamp?

From
Andy Colson
Date:
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

Re: How to typecast an integer into a timestamp?

From
Adrian Klaver
Date:
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

Re: How to typecast an integer into a timestamp?

From
Bruno Boettcher
Date:
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

Re: How to typecast an integer into a timestamp?

From
Adrian Klaver
Date:
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

Re: How to typecast an integer into a timestamp?

From
Bruno Boettcher
Date:
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

Re: How to typecast an integer into a timestamp?

From
Jasen Betts
Date:
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