Question :
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?
Answer :
My use case is a simple end-user-facing application accessed from only folks in the ‘Europe/Berlin’ timezone. […]
My intuition is now to store the time as a
timestamp without time zonebecause […]Later I wouldn’t be able to refer to the actual timezone that post was published in.
That indicates a misunderstanding stemming from the unfortunate data type names:
timestamp without time zone(=timestamp)timestamp with time zone( =timestamptz)
Neither data type stores any time zone information whatsoever. timestamptz just adds input and output logic to take the timezone setting of the current session into account, and projects from / to UTC time accordingly.
Don’t be too embarrassed if that had you confused, it has happened to the best of us:
In your particular case
If your statement is reliable:
accessed from only folks in the ‘Europe/Berlin’ timezone.
… then you might as well just use timestamp. You know the accompanying time zone. And there is no conversion between input and output. You can convert to timestamptz on the fly with:
my_column AT TIME ZONE 'Europe/Berlin'
If your app should outgrow it’s current scope and you need to switch to timestamptz to easily facilitate input from multiple time zones, convert with:
ALTER TABLE tbl ALTER col TYPE timestamptz
USING my_column AT TIME ZONE 'Europe/Berlin'
Be sure to use the time zone name ‘Europe/Berlin’, not a hard-coded offset that would fail to adjust for DST variations, nor time zone abbreviation, which is just a name for a hard-coded offset – another one of those very common misunderstandings.
Or use timestmaptz right away to cover all eventualities.
Either way, to preserve timezones like you asked, you then need to store them explicitly (in an additional column), as that is not saved otherwise – and you can no longer just assume ‘Europe/Berlin’.
Related: