7

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?

rsmidt
  • 73
  • 4

1 Answers1

6

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 zone because [...]

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. timestamptz just adds input and output logic to take an offset appended to the timestamp literal or 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, and only "folks in the 'Europe/Berlin' timezone" access the data, then you might just use the type 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 its current scope and you switch to timestamptz to 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. A time zone abbreviation is just a name for a hard-coded offset - another source of misunderstandings.

Or use timestamptz right away to cover all eventualities. That's the safe default.

Either way, to preserve time zones like you asked, you need to store them explicitly (in an additional column), as that is not saved otherwise.

Related:

Erwin Brandstetter
  • 185,527
  • 28
  • 463
  • 633