Postgresql date timezone issue

Question

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!

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
– mu is too short, Commented Aug 8, 2016 at 20:13
hi - sorry, i pasted the wrong input & just updated my question. — Trung Tran
– Trung Tran, Commented Aug 8, 2016 at 20:25
So just a four hour timezone adjustment, same timestamp but displayed in a -04 timezone. — mu is too short
– mu is too short, Commented Aug 8, 2016 at 20:33

Matt Johnson-Pint · Accepted Answer · 2016-08-08 21:57:06Z

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.

answered Aug 8, 2016 at 21:57

Matt Johnson-Pint

244k75 gold badges468 silver badges621 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgresql date timezone issue

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related