Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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 and 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();
 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:
share|improve this question
up vote 40 down vote accepted

Getting the Unix timestamp from a postgres timestamp with time zone like now() is simple, as you say, just:

select extract(epoch from now());

Things only get complicated when you have timestamp without time zone field (or just timestamp which is the same thing). When you put timestamp with time zone data like now() into that field, it will be converted to a particular timezone (either explicitly with at time zone or by removing the timezone) and the timezone information will be lost. If you only ever work in a single time zone this might not matter as conversion back to timestamp with time zone is clever enough to cope with DST in the current time zone. Example for GMT/BST:

select '2011-03-27 00:59:00.0+00'::timestamptz::timestamp::timestamptz, 
       '2011-03-27 01:00:00.0+00'::timestamptz::timestamp::timestamptz;

      timestamptz       |      timestamptz
 2011-03-27 00:59:00+00 | 2011-03-27 02:00:00+01

note the following that confused me earlier:

create table my_table(id integer, created timestamp);

\d my_table
              Table "stack.my_table"
 Column  |            Type             | Modifiers
 id      | integer                     |
 created | timestamp without time zone |

insert into my_table(id, created) 
values(1, '1970-01-01 00:00:00+00'),(2, '1970-01-01 00:00:00+00'::timestamptz);

select * from my_table;

 id |       created
  1 | 1970-01-01 00:00:00
  2 | 1970-01-01 01:00:00
(2 rows)

select id, created, extract(epoch from created) from my_table;

 id |       created       | date_part
  1 | 1970-01-01 00:00:00 |     -3600
  2 | 1970-01-01 01:00:00 |         0
(2 rows)

this is because "PostgreSQL never examines the content of a literal string before determining its type, and therefore will treat both [...] as timestamp without time zone. To ensure that a literal is treated as timestamp with time zone, give it the correct explicit type"

share|improve this answer
SELECT extract(epoch from now() at time zone 'utc');

doesn't return the correct timestamp because postgres timezone conversion throws away timezone information from the result:


Syntax: timestamp without time zone AT TIME ZONE zone
Returns: timestamp with time zone
Treat given time stamp without time zone as located in the specified time zone

Syntax: timestamp with time zone AT TIME ZONE zone
Returns: timestamp without time zone
Convert given time stamp with time zone to the new time zone, with no time zone designation

afterwards, extract looks at timestamp without time zone and considers it to be a local time (although it is already utc in fact).

The correct way would be:

select now(),
       extract(epoch from now()),                                          -- correct
       extract(epoch from now() at time zone 'utc'),                       -- incorrect
       extract(epoch from now() at time zone 'utc' at time zone 'utc');    -- correct

          now                  |    date_part     |    date_part     |    date_part
 2014-10-14 10:19:23.726908+02 | 1413274763.72691 | 1413267563.72691 | 1413274763.72691
(1 row)

In the last line the first at time zone performs the conversion, the second one assigns new time zone to the result.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.