Sample Header Ad - 728x90

Preserving timezones explicitly in postgres

7 votes
1 answer
2000 views
I've been doing quite some research on different ways of 'seeing' time and how to properly map it in Postgres but I'm still not certain on what to actually use. Several articles recommend or rather persuade you to store dates as timestamp with time zone and never as just timestamp. I'm especially struggling with daylight saving times. My use case is a simple end-user-facing application accessed from only folks in the 'Europe/Berlin' timezone. Users write posts that get stored along with timestamps for creation and updates. Let's say a user publishes a post on 2020-01-01T10:00:00+01. If a user now reads that post on the **same day** it should display posted on 1st of January at 10 am. If that same article gets clicked in **Berlin in July** it should still say posted on 1st of January at 10 am regardless of the DST. My intuition is now to store the time as a timestamp without time zone because otherwise Postgres would convert that time to UTC and store it that way. Later I wouldn't be able to refer to the actual timezone that post was published in. In that case, it would read posted on 1st of January at 11 am (due to Berlin now beeing ahead two hours) which could confuse the author if they were to check the time they initially published the post. Are my thoughts correct and did I found one of the corner cases where to not use timestamp with time zone or am I missing something crucial here?
Asked by rsmidt (73 rep)
Feb 14, 2020, 05:16 PM
Last activity: Nov 20, 2024, 09:39 AM