Programmazioni ricorrenti e fusi orari in PostgreSQL: pattern

Perché i fusi orari e gli eventi ricorrenti vanno storti

La maggior parte dei bug del calendario non sono errori di matematica. Sono errori di significato. Salvi una cosa (un istante nel tempo), ma gli utenti si aspettano un'altra (un orario sul quadrante locale in un posto specifico). Questo divario è il motivo per cui le programmazioni ricorrenti e i fusi orari possono sembrare corretti nei test e poi rompersi appena arrivano utenti reali.

L'ora legale (DST) è il classico innesco. Uno spostamento come “ogni domenica alle 09:00” non è la stessa cosa che “ogni 7 giorni a partire da un timestamp iniziale”. Quando l'offset cambia, queste due idee si scostano di un'ora e il tuo calendario diventa silenziosamente sbagliato.

Il viaggio e i fusi orari misti aggiungono un altro livello. Una prenotazione potrebbe essere legata a un luogo fisico (una poltrona in un salone a Chicago), mentre chi la guarda è a Londra. Se tratti un orario legato al luogo come se fosse legato alla persona, mostrerai l'orario locale sbagliato ad almeno una delle parti.

Modalità di errore comuni:

• Generi ricorrenze aggiungendo un intervallo a un timestamp memorizzato, poi cambia la DST.
• Salvi “orari locali” senza le regole della zona, quindi non puoi ricostruire gli istanti voluti in seguito.
• Testi solo su date che non attraversano mai un confine di DST.
• Mischi “fuso orario dell'evento”, “fuso orario dell'utente” e “fuso orario del server” in una singola query.

Prima di scegliere uno schema, decidi cosa significa “corretto” per il tuo prodotto.

Per una prenotazione, “corretto” di solito significa: l'appuntamento avviene all'orario sul quadrante inteso nel fuso orario della sede, e chiunque lo visualizzi ottiene una conversione corretta.

Per un turno, “corretto” spesso significa: il turno inizia a un orario locale fisso per il negozio, anche se un dipendente sta viaggiando.

Quella singola decisione (programmazione legata a un luogo vs. a una persona) guida tutto il resto: cosa salvi, come generi le ricorrenze e come interroghi una vista calendario senza sorprese di un'ora.

Scegli il modello mentale giusto: istante vs orario locale

Molti bug derivano dal mescolare due idee diverse di tempo:

• Un istante: un momento assoluto che accade una sola volta.
• Una regola di orario locale: un orario sul quadrante come “ogni lunedì alle 9:00 a Parigi”.

Un istante è lo stesso ovunque. “2026-03-10 14:00 UTC” è un istante. Chiamate video, partenze di voli e “invia questa notifica esattamente in questo momento” sono di solito istanti.

L'orario locale è ciò che le persone leggono su un orologio in un luogo. “09:00 in Europe/Paris ogni giorno feriale” è orario locale. Orari di apertura, corsi ricorrenti e turni del personale sono solitamente ancorati al fuso orario di una posizione. Il fuso orario fa parte del significato, non è una preferenza di visualizzazione.

Una semplice regola pratica:

• Usa timestamptz per inizio/fine quando l'evento deve avvenire in un momento reale unico a livello mondiale.
• Usa timestamp without time zone per valori locali del quadrante che da soli non sono istanti, come “ogni lunedì alle 09:00”. Abbinalo a un identificatore di fuso orario e converti in istanti solo quando generi le occorrenze.
• Se gli utenti viaggiano, mostra gli orari nel fuso del visualizzatore, ma mantieni la programmazione ancorata al suo fuso.
• Non indovinare il fuso da offset come "+02:00". Gli offset non contengono le regole della DST.

Esempio: un turno ospedaliero è “Lun-Ven 09:00-17:00 America/New_York.” Nella settimana del cambio DST, il turno è comunque dalle 9 alle 17 localmente, anche se gli istanti UTC si spostano di un'ora.

Tipi PostgreSQL che contano (e cosa evitare)

La maggior parte dei bug del calendario parte da un tipo di colonna sbagliato. La chiave è separare un momento reale dall'aspettativa del quadrante.

Usa timestamptz per istanti reali: prenotazioni, timbrature, notifiche e tutto ciò che confronti tra utenti o regioni. PostgreSQL lo memorizza come istante assoluto e lo converte per la visualizzazione, quindi ordinamento e controlli di sovrapposizione funzionano come previsto.

Usa timestamp without time zone per valori locali del quadrante che non sono istanti di per sé, come “ogni lunedì alle 09:00” o “il negozio apre alle 10:00.” Abbinalo a un identificatore di fuso orario IANA, poi converti in istanti reali solo quando generi le occorrenze.

Per i pattern ricorrenti, i tipi base utili sono:

• date per eccezioni che interessano solo il giorno (festività)
• time per un orario di inizio giornaliero
• interval per durate (come un turno di 6 ore)

Memorizza il fuso orario come nome IANA (per esempio, America/New_York) in una colonna text (o in una piccola tabella di lookup). Gli offset come -0500 non bastano perché non includono le regole dell'ora legale.

Un set pratico per molte app:

• timestamptz per inizio/fine istantanei delle prenotazioni
• date per giorni di eccezione
• time per l'orario locale di inizio ricorrente
• interval per la durata
• text per l'ID del fuso IANA

Opzioni di modello dati per app di prenotazione e turni

Lo schema migliore dipende da quanto spesso cambiano le schedule e quanto avanti visualizzano le persone. Stai di solito scegliendo tra scrivere molte righe in anticipo o generarle quando qualcuno apre un calendario.

Opzione A: memorizza ogni occorrenza

Inserisci una riga per ogni turno o prenotazione (già espansa). È facile da interrogare e da ragionare. Il compromesso sono scritture pesanti e molteplici aggiornamenti quando una regola cambia.

Funziona bene quando gli eventi sono per lo più one-off, o quando crei occorrenze solo per un breve periodo futuro (per esempio, i prossimi 30 giorni).

Opzione B: memorizza una regola ed espandi a lettura

Memorizza una regola di schedule (come “settimanale il Lun e Mer alle 09:00 in America/New_York”) e genera le occorrenze per l'intervallo richiesto on demand.

È flessibile e leggero in termini di storage, ma le query diventano più complesse. Le viste mensili possono rallentare a meno che tu non cache i risultati.

Opzione C: regola più occorrenze memorizzate (ibrido)

Mantieni la regola come fonte di verità e memorizza anche le occorrenze generate per una finestra mobile (per esempio, 60-90 giorni). Quando la regola cambia, rigeneri la cache.

Questo è un buon default per app di turni: le viste mensili restano veloci, ma hai ancora un solo posto per modificare il pattern.

Un set pratico di tabelle:

• schedule: owner/resource, fuso orario, orario locale di inizio, durata, regola di ricorrenza
• occurrence: istanze espanse con start_at timestamptz, end_at timestamptz, più stato
• exception: marker “salta questa data” o “questa data è diversa”
• override: modifiche per occorrenza come orario di inizio cambiato, membro staff sostituito, flag annullato
• (opzionale) schedule_cache_state: range generato per ultimo in modo da sapere cosa riempire dopo

Per query su intervalli di calendario, indicizza per “mostrami tutto in questa finestra”:

• Su occurrence: btree (resource_id, start_at) e spesso btree (resource_id, end_at)
• Se interroghi spesso “si sovrappone all'intervallo”: una colonna generata tstzrange(start_at, end_at) più un indice gist

Rappresentare regole di ricorrenza senza renderle fragili

Le schedule ricorrenti si rompono quando la regola è troppo intelligente, troppo flessibile o salvata come blob non interrogabile. Un buon formato di regola è quello che la tua app può validare e che il team riesce a spiegare rapidamente.

Due approcci comuni:

• Campi personalizzati semplici per i pattern che effettivamente supporti (turni settimanali, date di fatturazione mensile).
• Regole in stile iCalendar (RRULE) quando devi importare/esportare calendari o supportare molte combinazioni.

Un compromesso pratico: permette un set limitato di opzioni, memorizzale in colonne e tratta qualsiasi stringa RRULE solo per l'interoperabilità.

Per esempio, una regola settimanale può essere espressa con campi come:

• freq (daily/weekly/monthly) e interval (ogni N)
• byweekday (un array di 0-6 o una bitmask)
• opzionale bymonthday (1-31) per regole mensili
• starts_at_local (la data+ora locale scelta dall'utente) e tzid
• opzionale until_date o count (evita di supportare entrambi a meno che non sia necessario)

Per i confini, preferisci memorizzare la durata (per esempio, 8 ore) invece di memorizzare un timestamp di fine per ogni occorrenza. La durata resta stabile quando gli orologi cambiano. Puoi comunque calcolare un tempo di fine per occorrenza come: inizio occorrenza + durata.

Quando espandi una regola, mantienila sicura e limitata:

• Espandi solo entro window_start e window_end.
• Aggiungi un piccolo buffer (per esempio, 1 giorno) per eventi notturni.
• Interrompi dopo un numero massimo di istanze (tipo 500).
• Filtra i candidati prima (per tzid, freq e data di inizio) prima di generare.

Passo dopo passo: costruire una schedule ricorrente sicura per DST

Un pattern affidabile è: tratta ogni occorrenza prima come un'idea del calendario locale (data + ora locale + fuso della sede), poi converti in istante solo quando hai bisogno di ordinare, controllare conflitti o mostrare.

1) Salva l'intento locale, non supposizioni UTC

Memorizza il fuso orario della schedule (nome IANA come America/New_York) più un orario locale di inizio (per esempio 09:00). Quel tempo locale è ciò che l'azienda intende, anche quando la DST cambia.

Memorizza anche una durata e confini chiari per la regola: una data di inizio e o una data di fine o un conteggio di ripetizioni. I confini prevengono i bug di “espansione infinita”.

2) Modella eccezioni e override separatamente

Usa due tabelle piccole: una per le date da saltare, una per le occorrenze cambiate. Chiavele con schedule_id + local_date così puoi abbinare la ricorrenza originale in modo pulito.

Una forma pratica appare così:

-- 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) Espandi solo dentro la finestra richiesta

Genera date locali candidate per l'intervallo che stai renderizzando (settimana, mese). Filtra per giorno della settimana, poi applica skip e override.

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) Converti per il visualizzatore solo alla fine

Mantieni start_utc come timestamptz per ordinamento, controlli di conflitto e prenotazioni. Solo quando mostri, converti al fuso del visualizzatore. Questo evita sorprese legate alla DST e mantiene coerenti le viste calendario.

Pattern di query per generare una vista calendario corretta

Una schermata calendario è solitamente una query su un intervallo: “mostrami tutto tra from_ts e to_ts.” Un pattern sicuro è:

1. Espandi solo i candidati in quella finestra.
2. Applica eccezioni/override.
3. Restituisci righe finali con start_at e end_at come timestamptz.

Espansione giornaliera o settimanale con generate_series

Per regole settimanali semplici (come “ogni Lun-Ven alle 09:00 locale”), genera date locali nel fuso della schedule, poi trasforma ogni data locale + orario locale in un istante.

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

Questo funziona bene perché la conversione a timestamptz avviene per occorrenza, quindi gli spostamenti DST vengono applicati nel giorno corretto.

Regole più complesse con CTE ricorsiva

Quando le regole dipendono da “n-esimo giorno della settimana”, gap o intervalli personalizzati, un CTE ricorsivo può generare ripetutamente la prossima occorrenza finché non supera to_ts. Tieni la ricorsione ancorata alla finestra in modo che non possa girare all'infinito.

Dopo aver ottenuto le righe candidate, applica override e cancellazioni unendo le tabelle di eccezione su (rule_id, start_at) o su una chiave locale come (rule_id, local_date). Se esiste un record di cancellazione, rimuovi la riga. Se esiste un override, sostituisci start_at/end_at con i valori dell'override.

Pattern di performance che contano di più:

• Vincola l'intervallo presto: filtra le regole prima, poi espandi solo entro [from_ts, to_ts).
• Indicizza tabelle di eccezione/override su (rule_id, start_at) o (rule_id, local_date).
• Evita di espandere anni di dati per una vista mensile.
• Cache le occorrenze espanse solo se puoi invalidarle pulitamente quando le regole cambiano.

Gestire eccezioni e override in modo pulito

Le schedule ricorrenti sono utili solo se puoi interromperle in modo sicuro. Nelle app di prenotazione e turni, la “settimana normale” è la regola base e tutto il resto è eccezione: festività, cancellazioni, appuntamenti spostati o scambi di personale. Se le eccezioni vengono aggiunte in seguito, le viste calendario derivano e appaiono duplicati.

Mantieni separate tre concetti:

• Una schedule base (la regola ricorrente e il suo fuso)
• Skips (date o occorrenze che non devono accadere)
• Overrides (un'occorrenza che esiste ma con dettagli cambiati)

Usa un ordine di precedenza fisso

Scegli un ordine e mantienilo coerente. Una scelta comune:

1. Genera candidati dalla ricorrenza base.
2. Applica gli override (sostituisci la generata).
3. Applica gli skip (nascondi la riga).

Assicurati che la regola sia facile da spiegare agli utenti in una sola frase.

Evita duplicati quando un override sostituisce un'istanza

I duplicati di solito capitano quando una query restituisce sia l'occorrenza generata che la riga di override. Previeni questo con una chiave stabile:

• Dai a ogni istanza generata una chiave stabile, come (schedule_id, local_date, start_time, tzid).
• Memorizza quella chiave nella riga di override come “chiave occorrenza originale”.
• Aggiungi un vincolo unico così che esista al massimo un override per occorrenza base.

Poi, nelle query, escludi le occorrenze generate che hanno un override corrispondente e unisci le righe di override.

Mantieni auditability senza attrito

Le eccezioni sono dove nascono le controversie (“Chi ha cambiato il mio turno?”). Aggiungi campi di audit basilari su skips e overrides: created_by, created_at, updated_by, updated_at e un motivo opzionale.

Errori comuni che causano bug di un'ora

La maggior parte dei bug di un'ora viene dal confondere due significati di tempo: un istante (un punto sulla timeline UTC) e una lettura dell'orologio locale (come 09:00 ogni lunedì a New York).

Un errore classico è salvare una regola locale come timestamptz. Se salvi “lunedì alle 09:00 America/New_York” come un singolo timestamptz, hai già scelto una data specifica (e lo stato DST). Più tardi, quando generi i lunedì futuri, l'intento originale (“sempre 09:00 locale”) è andato.

Un'altra causa frequente è affidarsi a offset UTC fissi come -05:00 invece che a un nome di zona IANA. Gli offset non includono le regole DST. Salva l'ID della zona (per esempio, America/New_York) e lascia che PostgreSQL applichi le regole corrette per ogni data.

Fai attenzione a quando converti. Se converti in UTC troppo presto durante la generazione di una ricorrenza, puoi congelare un offset DST e applicarlo a ogni occorrenza. Un pattern più sicuro è: genera le occorrenze in termini locali (data + ora locale + zona), poi converti ogni occorrenza in un istante.

Errori che compaiono ripetutamente:

• Usare timestamptz per memorizzare un orario locale ricorrente (invece serviva time + tzid + una regola).
• Memorizzare solo un offset, non il nome zona IANA.
• Convertire durante la generazione delle ricorrenze invece che alla fine.
• Espandere ricorrenze “per sempre” senza una finestra temporale rigorosa.
• Non testare la settimana di inizio e la settimana di fine della DST.

Un test semplice che cattura la maggior parte dei problemi: scegli un fuso con DST, crea un turno settimanale alle 09:00 e renderizza un calendario di due mesi che attraversa un cambio DST. Verifica che ogni istanza sia mostrata come 09:00 locale, anche se gli istanti UTC sottostanti differiscono.

Checklist rapida prima della pubblicazione

Prima del rilascio, controlla le basi:

• Ogni schedule è legata a un luogo (o unità aziendale) con un fuso orario nominato, memorizzato sulla schedule stessa.
• Salvi ID zona IANA (come America/New_York), non offset grezzi.
• L'espansione delle ricorrenze genera occorrenze solo all'interno della finestra richiesta.
• Eccezioni e override hanno un ordine di precedenza unico e documentato.
• Testi le settimane di cambio DST e un visualizzatore in un fuso diverso dalla schedule.

Esegui un dry run realistico: un negozio in Europe/Berlin ha un turno settimanale alle 09:00 ora locale. Un manager lo guarda da America/Los_Angeles. Conferma che il turno rimanga alle 09:00 ora di Berlino ogni settimana, anche quando le regioni attraversano la DST in date diverse.

Esempio: turni settimanali con una festività e cambio DST

Una piccola clinica ha un turno ricorrente: ogni lunedì, 09:00-17:00 nel fuso locale della clinica (America/New_York). La clinica chiude per una festività in un lunedì specifico. Un membro del personale sta viaggiando in Europa per due settimane, ma la schedule della clinica deve restare legata all'orologio locale della clinica, non alla posizione attuale del dipendente.

Per farlo funzionare correttamente:

• Salva una regola di ricorrenza ancorata a date locali (weekday = Monday, orari locali = 09:00-17:00).
• Salva il fuso della schedule (America/New_York).
• Salva una data di inizio efficace così la regola ha un'ancora chiara.
• Salva un'eccezione per cancellare il lunedì festivo (e override per cambi one-off).

Ora renderizza un intervallo di calendario di due settimane che include un cambio DST a New York. La query genera i lunedì in quell'intervallo di date locali, allega gli orari locali della clinica e poi converte ogni occorrenza in un istante assoluto (timestamptz). Poiché la conversione avviene per occorrenza, la DST viene gestita nel giorno giusto.

Visori diversi vedono orari locali diversi per lo stesso istante:

• Un manager a Los Angeles lo vede prima sull'orologio.
• Un dipendente a Berlino lo vede più tardi sull'orologio.

La clinica ottiene comunque ciò che voleva: 09:00-17:00 ora di New York, ogni lunedì non cancellato.

Prossimi passi: implementare, testare e mantenere

Blinda il tuo approccio al tempo presto: salverai solo regole, solo occorrenze o un ibrido? Per molti prodotti di prenotazione e turni, un ibrido funziona bene: conserva la regola come fonte di verità, memorizza una cache mobile se serve e salva eccezioni e override come righe concrete.

Metti per iscritto il tuo “contratto temporale” in un posto: cosa conta come istante, cosa conta come orario locale e quali colonne memorizzano ciascuno. Questo evita che un endpoint restituisca orario locale mentre un altro torna UTC.

Tieni la generazione delle ricorrenze in un unico modulo, non in frammenti SQL sparsi. Se mai cambi il modo in cui interpreti “09:00 AM ora locale”, vuoi aggiornare un solo posto.

Se stai costruendo uno strumento di scheduling senza scrivere tutto a mano, AppMaster (appmaster.io) è una soluzione pratica per questo tipo di lavoro: puoi modellare il database nel suo Data Designer, costruire la logica di ricorrenza e eccezione nei processi business e ottenere comunque backend e codice per l'app reali.