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.

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)?

share|improve this question

migrated from serverfault.com Dec 7 '12 at 1:10

This question came from our site for professional system and network administrators.

    
PostgreSQL 9.1: 1351746099.69961 MySQL (!!) 5.0: 1351746099 Looks like you have a problem with version 8.4, not 9.2 –  Frank Heikens Dec 7 '12 at 8:26
add comment

1 Answer 1

up vote 6 down vote accepted

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.

share|improve this answer
add comment

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.