0

I have a pg database with a column type timestamp with time zone. I inserted the following date:

2016-08-01 00:00:00 GMT

However, in the database, it shows up as:

2016-07-31 20:00:00-04

Does anyone know what might be going on?

Thanks in advance!

Trung Tran
  • 13,141
  • 42
  • 113
  • 200
  • Are you sure that input results in that display? A difference of a couple hours would be a timezone issue (hence the `-04`) but going from 08-16 to 07-31 is a bit much. BTW, why use a timestamp column for a date when there is a perfectly serviceable date type? – mu is too short Aug 08 '16 at 20:13
  • 1
    hi - sorry, i pasted the wrong input & just updated my question. – Trung Tran Aug 08 '16 at 20:25
  • 2
    So just a four hour timezone adjustment, same timestamp but displayed in a -04 timezone. – mu is too short Aug 08 '16 at 20:33
  • Possible duplicate of [postgres default timezone](http://stackoverflow.com/questions/6663765/postgres-default-timezone) – Basil Bourque Aug 08 '16 at 20:46

1 Answers1

3

Despite the name, TIMESTAMP WITH TIME ZONE doesn't actually store the time zone. It uses the session's time zone to normalize to UTC, and stores UTC. On retrieval it converts back from UTC to the session time zone.

You can change the session time zone by using the SET TIME ZONE command. Preferably, you should use the standard IANA time zone identifiers. For example:

SET TIME ZONE 'Europe/Paris'

or

SET TIME ZONE 'UTC'

Alternatively use the TIMESTAMP [WITHOUT TIME ZONE] type instead, which does no conversions.

Matt Johnson-Pint
  • 230,703
  • 74
  • 448
  • 575