Unix timestamp is the number of seconds since midnight UTC January 1, 1970.
How do I get the correct unix timestamp from PostgreSQL?
When comparing to currenttimestamp.com and timestamp.1e5b.de I don't get the expected time from PostgreSQL:
This returns the correct timestamp:
SELECT extract(epoch from now());
While this doesn't:
SELECT extract(epoch from now() at time zone 'utc');
I live in time zone UTC +02. What is the correct way to get the current unix timestamp from PostgreSQL?
This returns the correct time and time zone:
SELECT now();
now
-------------------------------
2011-05-18 10:34:10.820464+02
Another comparison:
select now(),
extract(epoch from now()),
extract(epoch from now() at time zone 'utc');
now | date_part | date_part
-------------------------------+------------------+------------------
2011-05-18 10:38:16.439332+02 | 1305707896.43933 | 1305700696.43933
(1 row)
Unix timestamp from the web sites:
1305707967
Best Answer
In postgres,
timestamp with time zone
can be abbreviated astimestamptz
, andtimestamp without time zone
astimestamp
. I will use the shorter type names for simplicity.Getting the Unix timestamp from a postgres
timestamptz
likenow()
is simple, as you say, just:That's really all you need to know about getting the absolute time from anything of type
timestamptz
, includingnow()
.Things only get complicated when you have a
timestamp
field.When you put
timestamptz
data likenow()
into that field, it will first be converted to a particular timezone (either explicitly withat time zone
or by converting to the session timezone) and the timezone information is discarded. It no longer refers to an absolute time. This is why you don't usually want to store timestamps astimestamp
and would normally usetimestamptz
— maybe a film gets released at 6pm on a particular date in every timezone, that's the kind of use case.If you only ever work in a single time zone you might get away with (mis)using
timestamp
. Conversion back totimestamptz
is clever enough to cope with DST, and the timestamps are assumed, for conversion purposes, to be in the current time zone. Here's an example for GMT/BST:DBFiddle
But, note the following confusing behaviour:
DBFiddle
This is because: