Wiederkehrende Zeitpläne und Zeitzonen in PostgreSQL: Muster

Warum Zeitzonen und wiederkehrende Ereignisse schiefgehen

Die meisten Kalenderfehler sind keine Mathefehler. Es sind Bedeutungsfehler. Du speicherst eine Sache (einen Zeitpunkt/Instant), aber Nutzer erwarten etwas anderes (eine lokale Uhrzeit an einem bestimmten Ort). Diese Lücke ist der Grund, warum wiederkehrende Zeitpläne und Zeitzonen in Tests korrekt aussehen können und dann in der Realität kaputtgehen.

Daylight Saving Time (DST) ist der klassische Auslöser. „Jeden Sonntag um 09:00“ ist nicht dasselbe wie „alle 7 Tage ab einem Start-Zeitstempel“. Wenn sich der Offset ändert, driften diese beiden Auffassungen um eine Stunde auseinander und dein Kalender wird stillschweigend falsch.

Reisen und gemischte Zeitzonen verschärfen das Problem. Eine Buchung kann an einen physischen Ort gebunden sein (ein Friseurstuhl in Chicago), während die Person, die ihn ansieht, in London sitzt. Behandelst du einen ortsgebundenen Zeitplan wie einen personenbezogenen, zeigst du mindestens einer Seite die falsche lokale Zeit.

Häufige Fehlerbilder:

• Du erzeugst Wiederholungen, indem du einem gespeicherten Zeitstempel ein Intervall hinzufügst – dann ändert sich DST.
• Du speicherst „lokale Zeiten“ ohne die Zonendefinitionen, sodass du die beabsichtigten Instants später nicht rekonstruieren kannst.
• Du testest nur mit Daten, die nie eine DST-Grenze überschreiten.
• Du mischst „Event-Zeitzone“, „User-Zeitzone“ und „Server-Zeitzone“ in einer Abfrage.

Bevor du ein Schema wählst, entscheide, was „korrekt“ für dein Produkt bedeutet.

Bei einer Buchung heißt „korrekt“ meist: der Termin findet zur beabsichtigten Wanduhrzeit in der Zeitzone des Veranstaltungsorts statt, und jede Person, die ihn ansieht, erhält eine korrekte Umrechnung.

Bei einer Schicht bedeutet „korrekt“ oft: die Schicht beginnt zu einer festen lokalen Uhrzeit für das Geschäft, selbst wenn ein Mitarbeiter unterwegs ist.

Diese Entscheidung (Zeitplan an Ort vs. Person binden) bestimmt alles andere: was du speicherst, wie du Wiederholungen erzeugst und wie du eine Kalenderansicht ohne einstündige Überraschungen abfragst.

Wähle das richtige Denkmodell: Instant vs. lokale Zeit

Viele Fehler entstehen durch das Vermischen von zwei Zeitkonzepten:

• Ein Instant: ein absoluter Moment, der einmal stattfindet.
• Eine lokale Zeitregel: eine Wanduhrzeit wie „jeden Montag um 9:00 Uhr in Europe/Paris“.

Ein Instant ist überall gleich. „2026-03-10 14:00 UTC“ ist ein Instant. Videokonferenzen, Flugabflüge und „sende diese Benachrichtigung genau zu diesem Moment“ sind typischerweise Instants.

Lokale Zeit ist das, was Menschen auf einer Uhr an einem Ort ablesen. „9:00 Uhr in Europe/Paris an jedem Werktag“ ist lokale Zeit. Öffnungszeiten, wiederkehrende Kurse und Mitarbeiterschichten sind meist an die Zeitzone eines Ortes gebunden. Die Zeitzone ist Teil der Bedeutung, nicht nur eine Darstellungspräferenz.

Eine einfache Faustregel:

• Speichere start/end als Instants (timestamptz), wenn das Ereignis weltweit zu einem einzigen realen Moment stattfinden muss.
• Speichere lokale Datum- und Uhrzeit plus eine Zone-ID, wenn das Ereignis der Uhrzeit an einem Ort folgen soll.
• Wenn Nutzer reisen, zeige Zeiten in der Zeitzone des Betrachters, aber halte den Zeitplan an seiner Zone fest verankert.
• Errate keine Zone aus Offsets wie "+02:00". Offsets enthalten keine DST-Regeln.

Beispiel: Eine Krankenhausschicht ist „Mo–Fr 09:00–17:00 America/New_York“. In der Woche der DST-Umstellung bleibt die Schicht lokal 9 bis 17, obwohl sich die UTC-Instants um eine Stunde verschieben.

PostgreSQL-Typen, die wichtig sind (und was zu vermeiden ist)

Die meisten Kalenderfehler beginnen mit einem falschen Spaltentyp. Entscheidend ist, ein echtes Moment von einer Wanduhr-Erwartung zu trennen.

Verwende timestamptz für echte Instants: Buchungen, Clock-ins, Benachrichtigungen und alles, was du über Nutzer oder Regionen hinweg vergleichst. PostgreSQL speichert es als absoluten Zeitpunkt und konvertiert für die Anzeige, sodass Sortierung und Überlappungsprüfungen wie erwartet funktionieren.

Verwende timestamp without time zone für lokale Wanduhrwerte, die für sich genommen keine Instants sind, wie „jeden Montag um 09:00“. Kombiniere ihn mit einer Zeitzonen-ID und konvertiere erst beim Erzeugen von Vorkommnissen zu einem Instant.

Für wiederkehrende Muster helfen diese Basistypen:

• date für ausschließliche Tagesausnahmen (Feiertage)
• time für eine tägliche Startzeit
• interval für Dauern (z. B. eine 6-stündige Schicht)

Speichere die Zeitzone als IANA-Namen (z. B. America/New_York) in einer text-Spalte (oder in einer kleinen Lookup-Tabelle). Offsets wie -0500 reichen nicht, weil sie keine DST-Regeln enthalten.

Ein praktisches Set für viele Apps:

• timestamptz für Start-/End-Instants gebuchter Termine
• date für Ausnahmetage
• time für wiederkehrende lokale Startzeit
• interval für Dauer
• text für die IANA-Zeitzonen-ID

Datenmodell-Optionen für Buchungs- und Schicht-Apps

Das beste Schema hängt davon ab, wie oft sich Zeitpläne ändern und wie weit Nutzer vorausblicken. Du entscheidest meist zwischen vielen geschriebenen Zeilen im Voraus oder dem Generieren beim Lesen.

Option A: speichere jedes Vorkommnis

Füge für jede Schicht oder Buchung (bereits expandiert) eine Zeile ein. Das ist einfach abzufragen und nachzuvollziehen. Nachteil: viele Writes und viele Updates, wenn sich eine Regel ändert.

Gut, wenn Ereignisse meist einmalig sind oder du Vorkommnisse nur kurz voraus erzeugst (z. B. für die nächsten 30 Tage).

Option B: speichere die Regel und expandiere beim Lesen

Speichere eine Schedule-Regel (z. B. „wöchentlich Mo und Mi um 09:00 in America/New_York“) und generiere Vorkommnisse für den angefragten Bereich on demand.

Flexibel und speichersparend, aber Abfragen werden komplexer. Monatsansichten können langsamer sein, es sei denn, du cachest Ergebnisse.

Option C: Regel plus gecachte Vorkommnisse (Hybrid)

Behalte die Regel als Quelle der Wahrheit und speichere zusätzlich generierte Vorkommnisse für ein Rolling Window (z. B. 60–90 Tage). Wenn die Regel sich ändert, regenerierst du den Cache.

Guter Default für Schicht-Apps: Monatsansichten bleiben schnell, und du hast eine einzige Stelle, um das Muster zu bearbeiten.

Ein praktisches Tabellenset:

• schedule: owner/resource, time zone, local start time, duration, recurrence rule
• occurrence: expandierte Instanzen mit start_at timestamptz, end_at timestamptz plus Status
• exception: Marker „diesen Tag überspringen“ oder „an diesem Datum anders“
• override: pro-Vorkommnis-Änderungen wie geänderter Start, getauschtes Personal, Storno-Flag
• (optional) schedule_cache_state: zuletzt generierter Bereich, damit du weißt, was als Nächstes zu füllen ist

Für Kalenderbereich-Abfragen indexiere für „zeige mir alles in diesem Fenster“:

• Auf occurrence: btree (resource_id, start_at) und häufig btree (resource_id, end_at)
• Wenn du oft „überlappt Bereich“ abfragst: eine generierte tstzrange(start_at, end_at) plus ein gist-Index

Wiederholungsregeln darstellen, ohne sie fragil zu machen

Wiederkehrende Zeitpläne brechen, wenn die Regel zu clever, zu flexibel oder als nicht abfragbares Blob gespeichert ist. Ein gutes Regel-Format ist eines, das deine App validieren kann und das dein Team schnell erklären kann.

Zwei gängige Ansätze:

• Einfache, kundenspezifische Felder für die Muster, die du tatsächlich unterstützt (wöchentliche Schichten, monatliche Abrechnungsdaten).
• iCalendar-ähnliche Regeln (RRULE-Stil), wenn du Kalender importieren/exportieren oder viele Kombinationen unterstützen musst.

Ein praktischer Mittelweg: Erlaube eine begrenzte Menge an Optionen, speichere sie in Spalten und behandle RRULE-Strings nur als Austauschformat.

Beispiel für eine wöchentliche Schichtregel mit Feldern:

• freq (daily/weekly/monthly) und interval (jede N-te)
• byweekday (ein Array von 0–6 oder eine Bitmaske)
• optional bymonthday (1–31) für monatliche Regeln
• starts_at_local (das lokale Datum+Uhrzeit, das der Nutzer gewählt hat) und tzid
• optional until_date oder count (unterstütze nicht beide, außer du brauchst es wirklich)

Bei Grenzen speichere besser Dauer (z. B. 8 Stunden) statt für jedes Vorkommnis ein Endtimestamp. Dauer bleibt stabil, wenn Uhren umgestellt werden. Du kannst Endzeit pro Vorkommnis berechnen: start + duration.

Beim Expandieren einer Regel halte es sicher und begrenzt:

• Expandiere nur innerhalb von window_start und window_end.
• Füge einen kleinen Puffer hinzu (z. B. 1 Tag) für über Nacht gehende Ereignisse.
• Stoppe nach einer Maximalanzahl Instanzen (z. B. 500).
• Filtere Kandidaten zuerst (nach tzid, freq und Startdatum), bevor du generierst.

Schritt für Schritt: baue einen DST-sicheren wiederkehrenden Zeitplan

Ein verlässliches Muster ist: Behandle jedes Vorkommnis zuerst als lokale Kalenderidee (Datum + lokale Zeit + Ortszeitzone) und konvertiere erst dann zu einem Instant, wenn du sortieren, Konflikte prüfen oder anzeigen musst.

1. Speichere lokale Absicht, nicht UTC-Vermutungen

Speichere die Zeitzone des Zeitplans (IANA-Name wie America/New_York) plus eine lokale Startzeit (z. B. 09:00). Diese lokale Zeit ist das, was das Business meint, selbst wenn DST umschlägt.

Speichere außerdem eine Dauer und klare Grenzen für die Regel: ein Startdatum und entweder ein Enddatum oder eine Wiederholungsanzahl. Grenzen verhindern „unendliche Expansion“-Bugs.

2. Modelle Ausnahmen und Overrides getrennt

Nutze zwei kleine Tabellen: eine für übersprungene Daten, eine für geänderte Vorkommnisse. Keye sie mit schedule_id + local_date, damit du die ursprüngliche Wiederholung sauber matchen kannst.

Eine praktische Form:

-- core schedule
-- tz is the location time zone
-- start_time is local wall-clock time
schedule(id, tz text, start_date date, end_date date, start_time time, duration_mins int, by_dow int[])

schedule_skip(schedule_id, local_date date)

schedule_override(schedule_id, local_date date, new_start_time time, new_duration_mins int)

3. Expandiere nur innerhalb des angeforderten Fensters

Generiere Kandidaten-Daten für den Bereich, den du darstellst (Woche, Monat). Filtere nach Wochentag, wende dann Skips und Overrides an.

WITH days AS (
  SELECT d::date AS local_date
  FROM generate_series($1::date, $2::date, interval '1 day') d
), base AS (
  SELECT s.id, s.tz, days.local_date,
         make_timestamp(extract(year from days.local_date)::int,
                        extract(month from days.local_date)::int,
                        extract(day from days.local_date)::int,
                        extract(hour from s.start_time)::int,
                        extract(minute from s.start_time)::int, 0) AS local_start
  FROM schedule s
  JOIN days ON days.local_date BETWEEN s.start_date AND s.end_date
  WHERE extract(dow from days.local_date)::int = ANY (s.by_dow)
)
SELECT b.id,
       (b.local_start AT TIME ZONE b.tz) AS start_utc
FROM base b
LEFT JOIN schedule_skip sk
  ON sk.schedule_id = b.id AND sk.local_date = b.local_date
WHERE sk.schedule_id IS NULL;

4. Konvertiere für den Betrachter ganz zum Schluss

Behalte start_utc als timestamptz für Sortierung, Konfliktprüfungen und Buchungen. Erst bei der Anzeige konvertierst du in die Zeitzone des Betrachters. So vermeidest du DST-Überraschungen und hältst Kalenderansichten konsistent.

Abfragemuster, um eine korrekte Kalenderansicht zu erzeugen

Ein Kalenderbild ist normalerweise eine Bereichsabfrage: „Zeig mir alles zwischen from_ts und to_ts.“ Ein sicheres Muster ist:

1. Expandiere nur Kandidaten in diesem Fenster.
2. Wende Ausnahmen/Overrides an.
3. Gib finale Zeilen mit start_at und end_at als timestamptz aus.

Tägliche oder wöchentliche Expansion mit generate_series

Für einfache wöchentliche Regeln (z. B. „Mo–Fr um 09:00 lokal“) generiere lokale Daten in der Zeitzone des Zeitplans und wandle dann jedes lokale Datum + lokale Zeit in ein Instant um.

-- Inputs: :from_ts, :to_ts are timestamptz
-- rule.tz is an IANA zone like 'America/New_York'
WITH bounds AS (
  SELECT
    (:from_ts AT TIME ZONE rule.tz)::date AS from_local_date,
    (:to_ts   AT TIME ZONE rule.tz)::date AS to_local_date
  FROM rule
  WHERE rule.id = :rule_id
), days AS (
  SELECT d::date AS local_date
  FROM bounds, generate_series(from_local_date, to_local_date, interval '1 day') AS g(d)
)
SELECT
  (local_date + rule.start_local_time) AT TIME ZONE rule.tz AS start_at,
  (local_date + rule.end_local_time)   AT TIME ZONE rule.tz AS end_at
FROM rule
JOIN days ON true
WHERE EXTRACT(ISODOW FROM local_date) = ANY(rule.by_isodow);

Das funktioniert gut, weil die Konvertierung zu timestamptz pro Vorkommnis passiert und DST-Verschiebungen am richtigen Tag angewendet werden.

Komplexere Regeln mit einem rekursiven CTE

Wenn Regeln von „n-ter Wochentag“, Lücken oder benutzerdefinierten Intervallen abhängen, kann ein rekursiver CTE die nächste Vorkommnis wiederholt erzeugen, bis es nach to_ts fällt. Halte die Rekursion am Fenster verankert, damit sie nicht ewig läuft.

Nachdem du Kandidatenzeilen hast, wende Overrides und Stornierungen an, indem du Exception-Tabellen auf (rule_id, start_at) oder auf einem lokalen Schlüssel wie (rule_id, local_date) joinst. Gibt es eine Stornozeile, entferne die generierte Zeile. Gibt es ein Override, ersetze start_at/end_at durch die Override-Werte.

Performance-Muster, die am meisten zählen:

• Beschränke den Bereich früh: filtere Regeln zuerst, expandiere dann nur innerhalb [from_ts, to_ts).
• Indexiere Exception/Override-Tabellen auf (rule_id, start_at) oder (rule_id, local_date).
• Vermeide das Expandieren von Jahren an Daten für eine Monatsansicht.
• Cache expandierte Vorkommnisse nur, wenn du sie sauber invalidieren kannst, wenn Regeln sich ändern.

Ausnahmen und Overrides sauber handhaben

Wiederkehrende Zeitpläne sind nur nützlich, wenn du sie sicher unterbrechen kannst. In Buchungs- und Schicht-Apps ist die „normale“ Woche die Basisregel, und alles andere ist Ausnahme: Feiertage, Stornierungen, verschobene Termine oder Personaltäusche. Werden Ausnahmen später einfach dranmontiert, driften Kalenderansichten und Duplikate erscheinen.

Behalte drei Konzepte getrennt:

• Ein Basis-Schedule (die wiederkehrende Regel und ihre Zeitzone)
• Skips (Daten oder Instanzen, die nicht stattfinden sollen)
• Overrides (ein Vorkommnis existiert, aber mit geänderten Details)

Verwende eine feste Präzedenzordnung

Wähle eine Reihenfolge und halte sie konstant. Eine übliche Wahl:

1. Generiere Kandidaten aus der Basisregel.
2. Wende Overrides an (ersetze das generierte Vorkommnis).
3. Wende Skips an (blende es aus).

Stelle sicher, dass sich die Regel in einem Satz gegenüber Nutzern erklären lässt.

Duplikate vermeiden, wenn ein Override ein Vorkommnis ersetzt

Duplikate entstehen meist, wenn eine Abfrage sowohl das generierte Vorkommnis als auch die Override-Zeile zurückgibt. Verhindere das mit einem stabilen Schlüssel:

• Gib jedem generierten Vorkommnis einen stabilen Schlüssel, z. B. (schedule_id, local_date, start_time, tzid).
• Speichere diesen Schlüssel auf der Override-Zeile als „original occurrence key“.
• Füge eine eindeutige Einschränkung hinzu, sodass nur ein Override pro Basisvorkommnis existiert.

Dann schließe in Abfragen generierte Vorkommnisse aus, die ein passendes Override haben, und unioniere stattdessen die Override-Zeilen.

Auditfähigkeit ohne Reibung

Ausnahmen sind Orte für Streitigkeiten („Wer hat meine Schicht geändert?“). Füge einfache Audit-Felder auf Skips und Overrides hinzu: created_by, created_at, updated_by, updated_at und einen optionalen Grund.

Typische Fehler, die Off-by-one-hour-Bugs verursachen

Die meisten einstündigen Bugs entstehen durch das Vermischen zweier Bedeutungen von Zeit: ein Instant (ein Punkt auf der UTC-Zeitachse) und eine lokale Uhrzeit (z. B. 09:00 jeden Montag in New York).

Ein klassischer Fehler ist, eine lokale Wanduhrregel als timestamptz zu speichern. Wenn du „Montags um 09:00 America/New_York“ als einzigen timestamptz speicherst, hast du bereits ein konkretes Datum (und einen DST-Zustand) gewählt. Wenn du später weitere Montage erzeugst, ist die ursprüngliche Absicht („immer 09:00 lokal“) verloren.

Eine weitere häufige Ursache ist das Verlassen auf feste UTC-Offsets wie -05:00 statt auf einen IANA-Zonennamen. Offsets enthalten keine DST-Regeln. Speichere die Zone-ID (z. B. America/New_York) und lasse PostgreSQL die korrekten Regeln für jedes Datum anwenden.

Sei vorsichtig, wann du konvertierst. Wenn du zu früh in UTC konvertierst, während du eine Wiederholung erzeugst, kannst du einen DST-Offset einfrieren und auf jede Vorkommnis anwenden. Ein sicheres Muster ist: generiere Vorkommnisse in lokalen Begriffen (Datum + lokale Zeit + Zone) und konvertiere dann jede Vorkommnis in ein Instant.

Wiederkehrende Fehler:

• timestamptz zum Speichern einer wiederkehrenden lokalen Uhrzeit verwenden (du brauchst time + tzid + Regel).
• Nur einen Offset speichern, nicht den IANA-Zonennamen.
• Während der Generierung zu konvertieren statt am Ende.
• „Für immer“ expandierende Regeln ohne hartes Zeitfenster.
• Nicht die Wochen der DST-Umstellung testen.

Ein einfacher Test, der die meisten Probleme aufdeckt: Wähle eine Zone mit DST, erstelle eine wöchentliche 09:00-Schicht und rendere einen zwei Monate langen Kalender, der eine DST-Änderung überquert. Überprüfe, dass jede Instanz als 09:00 lokal angezeigt wird, auch wenn die zugrunde liegenden UTC-Instants unterschiedlich sind.

Schnelle Checkliste vor dem Release

Bevor du veröffentlichst, prüfe die Grundlagen:

• Jeder Zeitplan ist an einen Ort (oder Geschäftsbereich) mit einer benannten Zeitzone gebunden, gespeichert auf dem Schedule selbst.
• Du speicherst IANA-Zonen-IDs (wie America/New_York), nicht rohe Offsets.
• Die Wiederholungsexpansion erzeugt Vorkommnisse nur innerhalb des angefragten Bereichs.
• Ausnahmen und Overrides haben eine einzelne, dokumentierte Präzedenzordnung.
• Du testest die Wochen der DST-Änderung und einen Betrachter in einer anderen Zeitzone als der Schedule.

Mache einen realistischen Trockenlauf: Ein Laden in Europe/Berlin hat eine wöchentliche Schicht um 09:00 lokale Zeit. Ein Manager sieht sie aus America/Los_Angeles. Bestätige, dass die Schicht jede Woche 09:00 Berliner Zeit bleibt, selbst wenn die Regionen ihre DST an unterschiedlichen Daten umstellen.

Beispiel: wöchentliche Mitarbeiterschichten mit Feiertag und DST-Änderung

Eine kleine Klinik hat eine wiederkehrende Schicht: jeden Montag 09:00–17:00 in der lokalen Zeitzone der Klinik (America/New_York). Die Klinik ist an einem bestimmten Montag wegen eines Feiertags geschlossen. Ein Mitarbeiter reist zwei Wochen durch Europa, aber der Klinikplan muss an der Wanduhrzeit der Klinik bleiben, nicht an der aktuellen Location des Mitarbeiters.

So verhält sich das korrekt:

• Speichere eine Regel, die an lokale Daten verankert ist (Wochentag = Montag, lokale Zeiten = 09:00–17:00).
• Speichere die Zeitzone des Zeitplans (America/New_York).
• Speichere ein effektives Startdatum, damit die Regel einen klaren Anker hat.
• Speichere eine Ausnahme, um den Feiertags-Montag zu stornieren (und Overrides für Einmaländerungen).

Nun rendere einen zweiwöchigen Kalenderbereich, der eine DST-Änderung in New York enthält. Die Abfrage erzeugt Montage in diesem lokalen Datumsbereich, hängt die lokalen Zeiten der Klinik an und konvertiert jede Instanz dann in ein absolutes Instant (timestamptz). Weil die Konvertierung pro Vorkommnis passiert, wird DST am richtigen Tag berücksichtigt.

Verschiedene Betrachter sehen unterschiedliche lokale Uhrzeiten für denselben Instant:

• Ein Manager in Los Angeles sieht ihn früher auf der Uhr.
• Ein reisender Mitarbeiter in Berlin sieht ihn später auf der Uhr.

Die Klinik erhält weiterhin das Gewünschte: jeden Montag, der nicht storniert ist, 09:00–17:00 New York-Zeit.

Nächste Schritte: implementieren, testen und wartbar halten

Lege deine Vorgehensweise zur Zeit früh fest: speicherst du nur Regeln, nur Vorkommnisse oder ein Hybrid? Für viele Buchungs- und Schichtprodukte eignet sich ein Hybrid: die Regel als Quelle der Wahrheit, ein Rolling-Cache bei Bedarf und Ausnahmen/Overrides als konkrete Zeilen.

Schreibe deinen „Zeitvertrag“ an einem Ort nieder: was als Instant zählt, was als lokale Wanduhrzeit zählt und welche Spalten jeweils was speichern. Das verhindert Drift, bei dem ein Endpoint lokale Zeit zurückgibt und ein anderer UTC.

Halte die Wiederholungslogik als ein Modul und nicht in verstreuten SQL-Snippets. Wenn du einmal änderst, wie „9:00 AM lokal“ interpretiert wird, willst du nur eine Stelle updaten.

Wenn du ein Scheduling-Tool baust, ohne alles per Hand zu codieren, ist AppMaster (appmaster.io) ein praktischer Fit für diese Art Arbeit: Du kannst die Datenbank im Data Designer modellieren, Wiederholungs- und Ausnahme-Logik in Business-Prozessen abbilden und erhältst dennoch echten generierten Backend- und App-Code.