Different results for extract epoch on different PostgreSQL servers

Question

We convert time stamps to epoch, do some math on them and then convert them back to time stamps. All times in the database are TIMESTAMP WITHOUT TIME ZONE.

Since the switch to summer time here in the UK times are off by one hour on one server but not on the other so I did a little test:

SHOW SERVER_VERSION;
SHOW TIMEZONE;
SELECT extract(EPOCH FROM TIMESTAMP '1970-01-01');

On one server I get

 server_version 
----------------
9.1.15
(1 row)

 TimeZone 
----------
GB
(1 row)

 date_part 
-----------
         0
(1 row)

But on the other

 server_version 
----------------
9.3.6
(1 row)

 TimeZone 
----------
GB
(1 row)

 date_part 
-----------
     -3600
(1 row)

Are there any server settings which could be causing this?

Or did the behaviour of extract change after Postgres 9.1?

Solution

Yes, the behavior of extract changed in PostgreSQL version 9.2. From the release notes:

• Make EXTRACT(EPOCH FROM timestamp without time zone) measure the epoch from local midnight, not UTC midnight (Tom Lane)

  This change reverts an ill-considered change made in release 7.3. Measuring from UTC midnight was inconsistent because it made the result dependent on the timezone setting, which computations for timestamp without time zone should not be. The previous behavior remains available by casting the input value to timestamp with time zone.

This might be what is causing the difference, because according to the docs,

The SQL standard requires that writing just timestamp be equivalent to timestamp without time zone, and PostgreSQL honors that behavior.

As @unique_id suggests, using timestamp with time zone (a.k.a. timestamptz) should remove the inconsistency.

Comments

[peterwimsey]

Thanks, using ::timestamp with time zone as @unique_id suggested gives me -3600 on both servers. We convert back to timestamps after some calculations so the whole thing is rather like SELECT to_timestamp(extract(EPOCH FROM TIMESTAMP '1970-01-01'::timestamp with time zone))::timestamp without time zone. Tried a few other things and extract(EPOCH FROM TIMESTAMP '1970-01-01' AT TIME ZONE 'UTC') also seems to work.