Re: Convert a UNIX timestamp in a PostgreSQL INSERT statement - Mailing list pgsql-novice

From Tom Lane
Subject Re: Convert a UNIX timestamp in a PostgreSQL INSERT statement
Date
Msg-id 29246.1081132406@sss.pgh.pa.us
Whole thread Raw
In response to Re: Convert a UNIX timestamp in a PostgreSQL INSERT statement  (Richard Brooksby <rb@ravenbrook.com>)
List pgsql-novice
Richard Brooksby <rb@ravenbrook.com> writes:
> If you have a Unix-style timestamp (seconds since 1970-01-01 00:00) you
> can convert it to a PostgreSQL timestamp like this:

>    select timestamp '1970-01-01' + interval '953559481 seconds';

If it's really truly a Unix timestamp, that is seconds since 1970-01-01
00:00 GMT, you need to say "timestamptz" or "timestamp with time zone"
and specify that you want GMT zone.  The above references the interval
to 1970-01-01 midnight your local time, and will therefore be wrong by
the amount of your offset from GMT.

The recommended way is really

   select timestamptz 'epoch' + 953559481 * interval '1 second';

where 'epoch' is just a slightly more mnemonic way of writing
'1970-01-01 00:00 GMT'.  Using the number-times-interval operator as
I've done here is optional, but you'll find it's a good habit to get
into, because this way is much more convenient as soon as you start
doing anything even a little bit complicated.  The other way tends to
lead you into wanting to do ugly, error-prone things with concatenating
strings together and then converting them to interval...

            regards, tom lane

pgsql-novice by date:

Previous
From: joseph speigle
Date:
Subject: Re: problem with psql
Next
From: Nabil Sayegh
Date:
Subject: snowflaking