Why/how did the behavior of extract(epoch...) from "timestamp without time zone" fields change between PostgreSQL 8.4 and 9.2?

Question

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:

1. How has the behavior of extract(epoch...) on fields of type timestamp without time zone changed between PostgreSQL 8.4 and PostgreSQL 9.2?

2. Why did this change occur?

3. Is there any way to prevent this change without altering my schema or changing past data (i.e. a configuration-based fix)?

Answer 1

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.