Take the 2-minute tour ×
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. It's 100% free, no registration required.

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
share|improve this question

1 Answer 1

up vote 25 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
    
Ah, thanks your update is helpful. –  Jonas May 18 '11 at 10:00

Your Answer

 
discard

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.