Terugkerende schema's en tijdzones in PostgreSQL: patronen

Waarom tijdzones en terugkerende gebeurtenissen misgaan

De meeste fouten in agenda's zijn geen rekenfouten. Het zijn betekeningsfouten. Je slaat één ding op (een instant in de tijd), maar gebruikers verwachten iets anders (een lokale kloktijd op een specifieke plaats). Die kloof is waarom terugkerende schema's en tijdzones in tests soms goed lijken en zodra echte gebruikers komen stuklopen.

Zomertijd (DST) is de klassieke trigger. Een verschuiving die "elke zondag om 09:00" is, is niet hetzelfde als "elke 7 dagen vanaf een starttimestamp." Wanneer de offset verandert, lopen die twee ideeën een uur uit elkaar en wordt je agenda stilletjes onjuist.

Reizen en gemengde tijdzones voegen nog een laag toe. Een boeking kan verbonden zijn aan een fysieke plaats (een kappersstoel in Chicago), terwijl de persoon die het bekijkt in Londen zit. Als je een plaatsgebonden schema als persoonsgebonden behandelt, laat je minstens één kant de verkeerde lokale tijd zien.

Veelvoorkomende faalmechanismen:

• Je genereert recurrences door een interval bij een opgeslagen timestamp op te tellen, en dan verandert DST.
• Je slaat "lokale tijden" op zonder de zone-regels, zodat je de bedoelde instants later niet kunt herbouwen.
• Je test alleen data die nooit een DST-grens overschrijden.
• Je mixt "evenement-tijdzone", "gebruiker-tijdzone" en "server-tijdzone" in één query.

Voordat je een schema kiest, bepaal wat "correct" betekent voor je product.

Voor een boeking betekent "correct" meestal: de afspraak vindt plaats op de bedoelde kloktijd in de tijdzone van de locatie, en iedereen die het bekijkt krijgt een juiste conversie.

Voor een dienst (shift) betekent "correct" vaak: de dienst begint op een vaste lokale tijd voor de winkel, ook als een medewerker aan het reizen is.

Die ene beslissing (schema gekoppeld aan een plaats versus aan een persoon) bepaalt alles: wat je opslaat, hoe je recurrences genereert en hoe je een kalenderweergave opvraagt zonder een-uur verrassingen.

Kies het juiste denkkader: instant versus lokale tijd

Veel fouten komen voort uit het mixen van twee verschillende ideeën van tijd:

• Een instant: een absoluut moment dat eenmaal plaatsvindt.
• Een lokale tijdberegel: een kloktijd zoals "elke maandag om 9:00 in Parijs."

Een instant is overal hetzelfde. "2026-03-10 14:00 UTC" is een instant. Videogesprekken, vluchtvertrekken en "stuur deze notificatie precies op dit moment" zijn meestal instants.

Lokale tijd is wat mensen op een klok op een plaats aflezen. "9:00 in Europe/Paris elke werkdag" is lokale tijd. Winkeltijden, terugkerende lessen en diensten zijn meestal verankerd aan de tijdzone van een locatie. De tijdzone maakt deel uit van de betekenis, niet van de weergavevoorkeur.

Een eenvoudige vuistregel:

• Sla start/eind op als instants wanneer het event op één echt moment wereldwijd moet plaatsvinden.
• Sla lokale datum en lokale tijd plus een zone-ID op wanneer het event de klok op één plaats moet volgen.
• Als gebruikers reizen, toon tijden in de zone van de kijker, maar houd het schema verankerd aan zijn zone.
• Raad een zone niet af uit offsets zoals "+02:00". Offsets bevatten geen DST-regels.

Voorbeeld: een ziekenhuisdienst is "ma-vr 09:00-17:00 America/New_York." In de week van de DST-wijziging blijft de dienst lokaal 9 tot 5, ook al verschuiven de UTC-instants met een uur.

PostgreSQL-typen die ertoe doen (en wat te vermijden)

De meeste fouten in agenda's beginnen met één verkeerd kolomtype. Het belangrijkste is het scheiden van een echt moment van een klokverwachting.

Gebruik timestamptz voor echte instants: boekingen, klokins, notificaties en alles wat je vergelijkt over gebruikers of regio's. PostgreSQL slaat het op als een absoluut moment en zet het om voor weergave, dus ordening en overlapcontroles werken zoals verwacht.

Gebruik timestamp without time zone voor lokale klokwaarden die op zichzelf geen instants zijn, zoals "elke maandag om 09:00" of "winkel opent om 10:00." Koppel het aan een tijdzone-identificatie en zet pas om naar een echte instant wanneer je occurrences genereert.

Voor terugkerende patronen helpen de basis types:

• date voor uitzonderingsdagen (feesten)
• time voor een dagelijkse starttijd
• interval voor duur (zoals een dienst van 6 uur)

Sla de tijdzone op als een IANA-naam (bijvoorbeeld America/New_York) in een text-kolom (of een kleine lookup-tabel). Offsets zoals -0500 zijn niet genoeg omdat ze geen zomertijdregels bevatten.

Een praktisch setje voor veel apps:

• timestamptz voor start/eind-instants van geboekte afspraken
• date voor uitzonderingdagen
• time voor terugkerende lokale starttijd
• interval voor duur
• text voor de IANA time zone ID

Data-modelopties voor boekings- en dienstapps

Het beste schema hangt af van hoe vaak schema's veranderen en hoe ver vooruit mensen browsen. Je kiest meestal tussen veel rijen schrijven vooraf of genereren bij het openen van de kalender.

Optie A: sla elke occurrence op

Voeg één rij per dienst of boeking in (al uitgeklapt). Het is makkelijk te query'en en eenvoudig te begrijpen. De ruil is zware schrijfbewerking en veel updates wanneer een regel verandert.

Dit werkt goed wanneer events meestal eenmalig zijn, of wanneer je alleen occurrences kort van tevoren aanmaakt (bijvoorbeeld de komende 30 dagen).

Optie B: sla een regel op en breid bij lezen uit

Sla een schemaregel op (zoals "wekelijkse op ma en wo om 09:00 in America/New_York") en genereer occurrences voor het gevraagde bereik op aanvraag.

Het is flexibel en licht qua opslag, maar queries worden complexer. Maandoverzichten kunnen ook trager worden tenzij je resultaten cachet.

Optie C: regel plus gecachte occurrences (hybride)

Houd de regel als bron van waarheid, en sla ook gegenereerde occurrences op voor een rollend venster (bijvoorbeeld 60-90 dagen). Wanneer de regel verandert, genereer je de cache opnieuw.

Dit is vaak de beste keuze voor dienstapps: maandweergaven blijven snel, maar je hebt één plek om het patroon te bewerken.

Een praktische set tabellen:

• schedule: owner/resource, time zone, lokale starttijd, duur, recurrence rule
• occurrence: uitgeklapte instanties met start_at timestamptz, end_at timestamptz, plus status
• exception: "sla deze datum over" of "deze datum is anders" markers
• override: per-occurrence aanpassingen zoals gewijzigde starttijd, verwisselde medewerker, geannuleerd
• (optioneel) schedule_cache_state: laatst gegenereerd bereik zodat je weet wat je hierna moet vullen

Voor kalenderrange-queries, indexeer voor "toon alles in dit venster":

• Op occurrence: btree (resource_id, start_at) en vaak btree (resource_id, end_at)
• Als je vaak "overlaps range" queryt: een gegenereerde tstzrange(start_at, end_at) plus een gist-index

Recursieregels representeren zonder ze breekbaar te maken

Terugkerende schema's breken wanneer de regel te slim, te flexibel of opgeslagen als een ondoorzoekbare blob is. Een goed regel-formaat is er één die je app kan valideren en die je team snel kan uitleggen.

Twee veelvoorkomende benaderingen:

• Eenvoudige customvelden voor de patronen die je werkelijk ondersteunt (wekelijkse diensten, maandelijkse factureringsdata).
• iCalendar-achtige regels (RRULE-stijl) wanneer je moet importeren/exporteren of veel combinaties ondersteunen.

Een praktisch middenweg: sta een beperkt aantal opties toe, sla ze in kolommen op en behandel elke RRULE-string als alleen uitwisselingsformaat.

Bijvoorbeeld kan een wekelijkse dienstregel worden uitgedrukt met velden zoals:

• freq (daily/weekly/monthly) en interval (elke N)
• byweekday (een array van 0-6 of een bitmask)
• optioneel bymonthday (1-31) voor maandregels
• starts_at_local (de lokale datum+tijd die de gebruiker koos) en tzid
• optioneel until_date of count (ondersteun niet beide tenzij echt nodig)

Voor grenzen, geef de voorkeur aan het opslaan van duur (bijvoorbeeld 8 uur) in plaats van voor elke occurrence een eindtimestamp op te slaan. Duur blijft stabiel wanneer klokken verschuiven. Je kunt nog steeds een eindtijd per occurrence berekenen als: occurrence start + duur.

Bij het uitbreiden van een regel, houd het veilig en begrensd:

• Breid alleen binnen window_start en window_end uit.
• Voeg een kleine buffer toe (bijvoorbeeld 1 dag) voor overnight events.
• Stop na een maximum aantal instanties (zoals 500).
• Filter eerst kandidaten (op tzid, freq en startdatum) voordat je genereert.

Stap voor stap: bouw een DST-veilig terugkerend schema

Een betrouwbaar patroon is: behandel elke occurrence eerst als een lokaal kalenderidee (datum + lokale tijd + locatie-tijdzone) en zet pas om naar een instant wanneer je moet sorteren, conflicten controleren of het weergeven.

1) Sla lokale intentie op, geen UTC-raden

Sla de tijdzone van het schema op (IANA-naam zoals America/New_York) plus een lokale starttijd (bijvoorbeeld 09:00). Die lokale tijd is wat het bedrijf bedoelt, ook als DST verschuift.

Sla ook een duur en duidelijke grenzen voor de regel op: een startdatum en óf een einddatum óf een herhalingsaantal. Grenzen voorkomen "oneindige expansie"-fouten.

2) Modelleer uitzonderingen en overrides apart

Gebruik twee kleine tabellen: één voor overgeslagen data, één voor gewijzigde occurrences. Koppel ze op schedule_id + local_date zodat je de originele recurrence netjes kunt matchen.

Een praktische vorm ziet er zo uit:

-- 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) Breid alleen binnen het gevraagde venster uit

Genereer kandidaat-lokale data voor het bereik dat je rendert (week, maand). Filter op dag-van-de-week, en pas daarna skips en overrides toe.

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) Converteer voor de kijker pas aan het einde

Houd start_utc als timestamptz voor sorteren, conflicten en boekingen. Pas alleen bij weergave om naar de tijdzone van de kijker. Dit voorkomt DST-verrassingen en houdt kalenderweergaven consistent.

Querypatronen om een correcte kalenderweergave te genereren

Een kalenderscherm is meestal een range-query: "toon alles tussen from_ts en to_ts." Een veilig patroon is:

1. Breid alleen kandidaten binnen dat venster uit.
2. Pas uitzonderingen/overrides toe.
3. Geef finale rijen uit met start_at en end_at als timestamptz.

Dagelijkse of wekelijkse expansie met generate_series

Voor simpele wekelijkse regels (zoals "elke ma-vr om 09:00 lokaal") genereer je lokale data in de tijdzone van het schema en zet je elke lokale datum + lokale tijd om in een instant.

-- 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);

Dit werkt goed omdat de conversie naar timestamptz per occurrence gebeurt, dus DST-verschuivingen op de juiste dag worden toegepast.

Complexere regels met een recursieve CTE

Wanneer regels afhangen van "nth weekday", gaten of custom intervallen, kan een recursieve CTE de volgende occurrence herhaaldelijk genereren totdat hij voorbij to_ts is. Houd recursie verankerd aan het venster zodat hij niet eindeloos kan draaien.

Nadat je kandidaat-rijen hebt, pas je overrides en annuleringen toe door exception-tabellen te joinen op (rule_id, start_at) of op een lokale sleutel zoals (rule_id, local_date). Als er een cancel-record is, verwijder de rij. Als er een override is, vervang start_at/end_at met de override-waarden.

Prestaties die ertoe doen:

• Beperk het bereik vroeg: filter regels eerst, breid daarna alleen binnen [from_ts, to_ts) uit.
• Indexeer exception/override-tabellen op (rule_id, start_at) of (rule_id, local_date).
• Vermijd het uitklappen van jaren data voor een maandoverzicht.
• Cache uitgeklapte occurrences alleen als je ze netjes kunt ongeldig maken wanneer regels veranderen.

Uitzonderingen en overrides netjes afhandelen

Terugkerende schema's zijn alleen bruikbaar als je ze veilig kunt breken. In boekings- en dienstapps is de "normale" week de basisregel, en alles daarbuiten is een uitzondering: feestdagen, annuleringen, verplaatste afspraken of personeelswissels. Als uitzonderingen later erop geplakt worden, drijven kalenderweergaven uit en verschijnen duplicaten.

Houd drie concepten gescheiden:

• Een basis-schedule (de recursieregel en zijn tijdzone)
• Skips (data of instanties die niet mogen plaatsvinden)
• Overrides (een occurrence die bestaat maar met gewijzigde details)

Gebruik een vaste precedentievolgorde

Kies één volgorde en houd die consistent. Een gebruikelijke keuze:

1. Genereer kandidaten vanuit de basisrecurrence.
2. Pas overrides toe (vervang de gegenereerde).
3. Pas skips toe (verberg het).

Zorg dat de regel in één zin aan gebruikers uit te leggen is.

Voorkom duplicaten wanneer een override een instantie vervangt

Duplicaten ontstaan meestal wanneer een query zowel de gegenereerde occurrence als de override-rij teruggeeft. Voorkom dat met een stabiele sleutel:

• Geef elke gegenereerde instantie een stabiele sleutel, zoals (schedule_id, local_date, start_time, tzid).
• Sla die sleutel op in de override-rij als de "originele occurrence key."
• Voeg een unique constraint toe zodat er slechts één override per basis-occurrence bestaat.

In queries sluit je dan gegenereerde occurrences uit die een matchende override hebben en union je de override-rijen erbij.

Houd audit-trailing zonder frictie

Uitzonderingen zijn waar geschillen ontstaan ("Wie heeft mijn dienst veranderd?"). Voeg basis auditvelden toe op skips en overrides: created_by, created_at, updated_by, updated_at en een optionele reden.

Veelgemaakte fouten die één-uur-verschillen veroorzaken

De meeste één-uur-bugs komen van het verwarren van twee betekenissen van tijd: een instant (een punt op de UTC-tijdslijn) en een lokale kloklezing (zoals 09:00 elke maandag in New York).

Een klassieke fout is het opslaan van een lokale klokregel als timestamptz. Als je "maandagen om 09:00 America/New_York" als één timestamptz opslaat, heb je al een specifieke datum (en DST-status) gekozen. Later, wanneer je toekomstige maandagen genereert, is de oorspronkelijke intentie ("altijd 09:00 lokaal") verdwenen.

Een andere veelvoorkomende oorzaak is vertrouwen op vaste UTC-offsets zoals -05:00 in plaats van een IANA-zone naam. Offsets bevatten geen DST-regels. Sla de zone-ID op (bijvoorbeeld America/New_York) en laat PostgreSQL de juiste regels voor elke datum toepassen.

Wees voorzichtig met wanneer je converteert. Als je te vroeg naar UTC converteert tijdens het genereren van een recurrence, kun je een DST-offset vastzetten en die op elke occurrence toepassen. Een veiliger patroon is: genereer occurrences in lokale termen (datum + lokale tijd + zone), zet vervolgens elke occurrence afzonderlijk om in een instant.

Fouten die vaak terugkomen:

• Gebruik van timestamptz om een terugkerende lokale tijd-van-de-dag op te slaan (je had time + tzid + een regel nodig).
• Alleen een offset opslaan, niet de IANA-zone.
• Omzetten tijdens recurrence-generatie in plaats van aan het einde.
• "Forever" recurrences uitbreiden zonder een harde tijdslimiet.
• Niet testen tijdens de DST-startweek en DST-eindweek.

Een eenvoudige test die de meeste problemen vangt: kies een zone met DST, maak een wekelijkse 09:00 dienst en render een twee-maanden kalender die een DST-wijziging overschrijdt. Controleer dat elke instantie als 09:00 lokale tijd verschijnt, ook al verschillen de onderliggende UTC-instants.

Snelle checklist voordat je live gaat

Controleer de basis voordat je uitrolt:

• Elk schema is gekoppeld aan een plaats (of business unit) met een benoemde tijdzone, opgeslagen op het schema zelf.
• Je slaat IANA zone IDs op (zoals America/New_York), niet ruwe offsets.
• Recurrence-expansie genereert occurrences alleen binnen het gevraagde bereik.
• Uitzonderingen en overrides hebben één gedocumenteerde precedentievolgorde.
• Je test DST-wijzigingsweken en een kijker in een andere tijdzone dan het schema.

Doe één realistische droge run: een winkel in Europe/Berlin heeft een wekelijkse dienst om 09:00 lokale tijd. Een manager bekijkt die vanuit America/Los_Angeles. Bevestig dat de dienst elke week 09:00 Berlijnse tijd blijft, ook als beide regio's op verschillende data DST wijzigen.

Voorbeeld: wekelijkse diensten met een feestdag en DST-wijziging

Een kleine kliniek heeft één terugkerende dienst: elke maandag, 09:00-17:00 in de lokale tijdzone van de kliniek (America/New_York). De kliniek is gesloten op één specifieke maandag vanwege een feestdag. Een medewerker reist twee weken in Europa, maar het schema van de kliniek moet verankerd blijven aan de klok van de kliniek, niet aan de huidige locatie van de medewerker.

Om dit correct te laten werken:

• Sla een recursieregel op verankerd aan lokale data (weekday = Monday, lokale tijden = 09:00 tot 17:00).
• Sla de tijdzone van het schema op (America/New_York).
• Sla een effectieve startdatum op zodat de regel een duidelijk anker heeft.
• Sla een uitzondering op om de feestdag-maandag te annuleren (en overrides voor incidentele aanpassingen).

Render nu een kalenderbereik van twee weken dat een DST-wijziging in New York bevat. De query genereert de maandagen in dat lokale datumbereik, koppelt de lokale tijden van de kliniek eraan en zet vervolgens elke occurrence om in een absolute instant (timestamptz). Omdat de conversie per occurrence gebeurt, wordt DST op de juiste dag afgehandeld.

Verschillende kijkers zien verschillende lokale kloktijden voor hetzelfde instant:

• Een manager in Los Angeles ziet het vroeger op de klok.
• Een reizende medewerker in Berlijn ziet het later op de klok.

De kliniek krijgt nog steeds wat hij wil: 09:00 tot 17:00 New York-tijd, elke maandag die niet geannuleerd is.

Volgende stappen: implementeren, testen en onderhoudbaar houden

Leg je aanpak voor tijd vroeg vast: ga je alleen regels opslaan, alleen occurrences, of een hybride? Voor veel boekings- en dienstproducten werkt een hybride goed: houd de regel als bron van waarheid, sla een rollende cache op indien nodig en sla uitzonderingen en overrides als concrete rijen op.

Schrijf je "tijdcontract" ergens neer: wat telt als een instant, wat telt als lokale kloktijd en welke kolommen elk opslaan. Dit voorkomt dat één endpoint lokale tijd teruggeeft terwijl een ander UTC teruggeeft.

Houd recurrence-generatie als één module, niet verspreid over SQL-fragmenten. Als je ooit verandert hoe je "09:00 lokale tijd" interpreteert, wil je één plek waar je dat aanpast.

Als je een planningshulpmiddel bouwt zonder alles handmatig te coderen, is AppMaster (appmaster.io) een praktische keuze voor dit type werk: je kunt de database modelleren in de Data Designer, recurrence- en uitzonderingslogica bouwen in business processes en toch echte gegenereerde backend- en app-code krijgen.