Postgresql – Why/how did the behavior of extract(epoch…) from “timestamp without time zone” fields change between PostgreSQL 8.4 and 9.2

postgresqltimezone

Context:

We currently run PostgreSQL 8.4 in production, and are testing 9.2 internally for a future upgrade. Some issues have emerged regarding dates. The servers that run 8.4 versus 9.2 are identical in every way other than the PostgreSQL versions and configs. The same exact data is stored on both sets of servers; it is transferred using pg_dump and pg_restore.

In one part of our database, we store dates in a field of type timestamp without time zone.

Problem:

When accessing dates from that field, an extract(epoch..) request returns very different results depending on the Postgres version, like so:

#On Postgres 8.4. entered_timestamp is a "timestamp without time zone" type column:
SELECT entered_timestamp, extract(epoch from entered_timestamp) AS entered_timestamp from <table row>

 entered_timestamp      | entered_timestamp
 ----------------------------+-------------------
 2012-11-01 06:01:39.699612 |  1351774899.69961

But…

#On Postgres 9.2. All tables, schema, and data are identical, the SELECT statement is identical to the previous one:
SELECT entered_timestamp, extract(epoch from entered_timestamp) AS entered_timestamp from <table row>

 entered_timestamp      | entered_timestamp
 ----------------------------+-------------------
 2012-11-01 06:01:39.699612 |  1351749699.69961

As you can see, the returned timestamps are identical, but the returned epoch numbers are not; in fact, they are off by several hours. The server dates and Linux time zones on both servers are identical (within a second of each other). The Postgres time zones are also identical: running SELECT current_setting('timezone') on both servers returns the same data as well. Running SELECT now() on both servers returns near-identical values.

Question:

How has the behavior of extract(epoch...) on fields of type
timestamp without time zone changed between PostgreSQL 8.4 and
PostgreSQL 9.2?
Why did this change occur?
Is there any way to prevent this change without altering my schema
or changing past data (i.e. a configuration-based fix)?

Best Answer

Testing this on my own machine, I get 1351749699.69961 as the result on both PostgreSQL 8.4.10 and 9.2.1.

With PostgreSQL 8.4.10, the result changes according to the session timezone. With 9.2, it does not.

Likely this is the effect of this change: Measure epoch of timestamp-without-time-zone from local not UTC midnight. which is noted as a 9.2 compatibility change.

The suggested workaround in the change notes is to cast the timestamp to a timestamptz first: not just a configuration change.

Personally, I find the 9.2 behaviour more comfortable. The Unix epoch is defined as UTC, so this effectively means that plain timestamp values are interpreted as UTC: which is how I use them anyway.

Related Solutions

Postgresql – How to get the timestamp column in only milliseconds from PostgreSQL

Use EXTRACT and the UNIX-Timestamp

SELECT EXTRACT(EPOCH FROM TIMESTAMP '2011-05-17 10:40:28.876944') * 1000;

would give

1305621628876.94

Multiply it by 1000 to turn it into milliseconds. You can then convert it to whatever you want (decimal would be a good choice). Don't forget to keep the timezone in mind. JackPDouglas has such an example in his answer. Here is an excerpt from his answer (created being the column with your timetamp) that illustrates how to work with timezones:

SELECT EXTRACT(EPOCH FROM created AT TIME ZONE 'UTC') FROM my_table;

PostgreSQL – How to Get the Current Unix Timestamp

In postgres, timestamp with time zone can be abbreviated as timestamptz, and timestamp without time zone as timestamp. I will use the shorter type names for simplicity.

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

select extract(epoch from now());

That's really all you need to know about getting the absolute time from anything of type timestamptz, including now().

Things only get complicated when you have a timestamp field.

When you put timestamptz data like now() into that field, it will first be converted to a particular timezone (either explicitly with at 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 as timestamp and would normally use timestamptz — 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 to timestamptz 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:

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|
*/

_DBFiddle

But, note the following confusing behaviour:

set timezone to 0;

values(1, '1970-01-01 00:00:00+00'::timestamp::timestamptz)
    , (2, '1970-01-01 00:00:00+02'::timestamp::timestamptz);

/*
|column1|column2               |
|------:|:---------------------|
|      1|1970-01-01 00:00:00+00|
|      2|1970-01-01 00:00:00+00|
*/

_DBFiddle

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…In a literal that has been determined to be timestamp without time zone, PostgreSQL will silently ignore any time zone indication

Best Answer

Related Solutions

Postgresql – How to get the timestamp column in only milliseconds from PostgreSQL

PostgreSQL – How to Get the Current Unix Timestamp

Related Question