I'm trying to figure out how Postgres handles DST in combination with intervals. Specifically I want to allow my users to create events with recurring dates - e.g. everyday at 16:00 local time.
For what I'm doing I need to store the first date in the user's local time, and then add a number of days to it, without changing the time of day in the user's local time. I was hoping that timestamptz with a full timezone name (so it knows when to apply DST?) combined with simple 1 day intervals would do the job - but it fails at my simple example:
Germany uses CET (+1:00) and switches to CEST (+2:00) at 2:00 in the morning on March 28. Tunesia uses CET all year.
Thus I expected, that using a timestamptz on March 27 and adding 1 day to it, I'd see a different utc-offset in Berlin, and no change in Tunis - but they both changed the offset equally, as if Tunis was using DST:
select
'2021-03-27 16:00:00 Africa/Tunis'::timestamptz as "tunis_before_dst",
'2021-03-27 16:00:00 Africa/Tunis'::timestamptz + INTERVAL '1 day' as "tunis_after_dst",
'2021-03-27 16:00:00 Europe/Berlin'::timestamptz as "berlin_before_dst",
'2021-03-27 16:00:00 Europe/Berlin'::timestamptz + INTERVAL '1 day' as "berlin_after_dst"
results in:
tunis_before_dst: '2021-03-27 16:00:00+01'
tunis_after_dst: '2021-03-28 16:00:00+02'
berlin_before_dst: '2021-03-27 16:00:00+01'
berlin_after_dst: '2021-03-28 16:00:00+02'
Looking through pg_timezone_names, I can see that my Postgres instance is aware of Africa/Tunis not having DST - so I'm wondering why it's changing the UTC offset for it then.
I guess it's obvious that timezones and DST are very confusing to me, but am I doing something wrong handling them or is timezonetz not what I think it is?
Rant first. The concept of DST is breathtaking nonsense. Even the name is obvious BS. "Daylight Saving Time". No daylight has been saved. I can't believe the EU still did not manage to get rid of it, even though the overwhelming majority wants it gone, and it's been consensus to scrap it for a while now.
With that out of my system, the primary misunderstanding is this: you assume that the data type timestamp with time zone would store time zone information. It does not. Becomes obvious here:
Thus I expected, [...] I'd see a different utc-offset in Berlin, and no change in Tunis - but they both changed the offset equally
The time zone offset you see in the output is the offset determined by the current timezone setting of your session. Time zone serves as input / output modifier / decorator. Postgres always stores UTC time internally. And no time zone information whatsoever.
The type name is a bit deceiving there. It has been known to fool the best:
Once you've grasped that concept, the rest should become obvious.
To preserve the local time (wall clock time of day), use the data type timestamp without time zone (timestamp), or even just time (never use the broken timetz), and store time zone information additionally - ideally the time zone name ('Europe/Berlin' like you have it), not a time zone abbreviation or a numeric offset.
timestamp with time zone (timestamptz) is the right choice to store unique points in time, independent of any time zones. The time zone offset is just an input modifier. Both of the following literals result in the same timestamptz value exactly, because both time zones happen to apply the same offset at this time of the year:
'2021-03-27 16:00:00 Africa/Tunis'::timestamptz
'2021-03-27 16:00:00 Europe/Berlin'::timestamptz
But these differ by one hour, because the German offset has changed according to the local DST regime:
'2021-03-28 16:00:00 Africa/Tunis'::timestamptz
'2021-03-28 16:00:00 Europe/Berlin'::timestamptz
Related: