3

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?

d0n.key
  • 1,318
  • 2
  • 18
  • 39
  • https://dbfiddle.uk/?rdbms=postgres_13&fiddle=5f454066ca30a50d88f8adb94a41f061 – Abelisto Apr 10 '21 at 22:02
  • @Abelisto That one appears to work, but somehow before DST it shows Tunis at 15:00+0 and after at 16:00+1 - no real change, but why displayed differently? I would expect it to always be displayed as 16:00+1, since I create a timestamp at 16:00 in Tunis local time with it? – d0n.key Apr 11 '21 at 00:38
  • 1
    @d0n.key: The display in dbfiddle is governed by its time zone setting. See https://dbfiddle.uk/?rdbms=postgres_13&fiddle=c14aa7a31c8c9d676869cd0d4c936fdb – Erwin Brandstetter Apr 11 '21 at 01:00
  • Aside: you show results displayed with divergent offset. Postgres itself wouldn't do that in the same session (with the same `timezone` setting). – Erwin Brandstetter Apr 11 '21 at 16:47

1 Answers1

3

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:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • To be fair, the EU has postponed DST abolishment due to Covid interfering with companies adjusting to it. So it's likely that once my project is ready for release, DST isn't a thing anymore either way in EU states, but in a couple other ones still - and since it could always make an unwanted reappearance, I guess it's best to be prepared. – d0n.key Apr 12 '21 at 20:51
  • Yeah, you have to be prepared either way. Foolishness dies hard. If ever. – Erwin Brandstetter Apr 12 '21 at 23:11