0

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.

Alex Vyushkov
  • 650
  • 1
  • 6
  • 21
  • See this post: http://stackoverflow.com/questions/5876218/difference-between-timestamps-with-without-time-zone-in-postgresql – Crouching Kitten Jan 08 '17 at 19:46
  • Possible duplicate of [Difference between timestamps with/without time zone in PostgreSQL](http://stackoverflow.com/questions/5876218/difference-between-timestamps-with-without-time-zone-in-postgresql) – Crouching Kitten Jan 08 '17 at 19:46
  • What does psql output? – teppic Jan 09 '17 at 01:50
  • 1
    This looks like a rendering bug in your client. The "-05:44:38" timezone is particularly suspicious. – teppic Jan 09 '17 at 02:06

2 Answers2

1

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.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
0

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.

Alex Vyushkov
  • 650
  • 1
  • 6
  • 21