CODEWITHSUNDEEP
postgresqltimestamppostgresql-9.4timestamp-with-timezone
Alex Vyushkov
Alex Vyushkov

Reputation: 670

Postgresql stores timezone information in TIMESTAMP WITH TIMEZONE?

I'm trying to understand how PostgreSQL handles TIMESTAMP WITH TIMEZONE type, and I 'm running into the discrepancy between theory and practice.

According to the documentation and other stack overflow posts,

For timestamp with time zone, the internally stored value is always in UTC ... When a timestamp with time zone value is output, it is always converted from UTC to the current timezone zone.

I believe this means that all timestamps in any given SELECT statement will be in the same/current timezone. However, this is what I see: enter image description here

interview_timestamp is TIMESTAMP WITH TIMEZONE. This blows my mind.

Upvotes: 0

Views: 614

Answers (2)

Alex Vyushkov
Alex Vyushkov

Reputation: 670

Just for the record, this was really a client thing and it because an offset from UTC depends on the time of year (EDT: -4, EST: -5). Offset "-05:44:38" is correct too, this is offset in TZ database for EDT/EST for years prior to 1883.

Upvotes: 0

Laurenz Albe
Laurenz Albe

Reputation: 246163

It is strange that you sometimes see a time zone offset and sometimes not, I would blame that on the GUI you are using. Try with psql and see how the data look there.

The offset “-05:44:38” is perfectly normal, since time zones were introduced in the late 19th century and local solar time is used before that.

Upvotes: 1

Related Questions