Date-time pitfalls e timezone correctness

Il dashboard giornaliero mostra un calo alle 00:00 UTC, ma il team europeo vede vendite ancora nel giorno precedente e quello americano non ha finito la giornata. Il problema non è un arrotondamento: è una decisione temporale non dichiarata. Date-time pitfalls e timezone correctness porta SQL nel punto in cui calendario, timestamp e business day devono parlare la stessa lingua.

Una scena da cui partire

Leggi questa lezione con l’attenzione di chi deve firmare una metrica temporale. Ogni DATE_TRUNC, ogni conversione di timezone e ogni date spine decide quali eventi esistono in un periodo e quali spariscono dal report.

• Contesto: Quale fuso orario rappresenta davvero il fenomeno?
• Metodo: Quale controllo rivela buchi o duplicazioni nella serie?
• Applicazione: Come documenteresti la regola temporale prima di pubblicare la metrica?

Tipi di dato temporali: una mappa minima

Ogni database ha la sua nomenclatura, ma i concetti si riducono a tre tipi fondamentali:

Tipo	Contiene	Esempio	Quando usarlo
TIMESTAMP / DATETIME	Data + ora + (opzionalmente) timezone	2024-03-15 14:30:00+01	Eventi puntuali con fuso orario noto
DATE	Solo data, nessuna ora	2024-03-15	Aggregazioni giornaliere, compleanni, scadenze
INTERVAL	Durata tra due timestamp	3 days 4 hours	Calcolo di delta, scadenze, SLAs

La regola d’oro, formulata da Tom Kyte di Oracle in Expert Oracle Database Architecture (2005): salva sempre in UTC, converti in locale solo nel livello di presentazione. Questo principio, vecchio di 20 anni, viene violato quotidianamente in produzione.

Il problema delle timezone: tre insidie classiche

Insidia 1: Assumere che il server sia nel tuo fuso

Molti database hanno il fuso del server impostato a UTC. Ma se il server è a New York, CURRENT_DATE restituirà la data di New York, non la tua. Soluzione: mai fare affidamento sul fuso implicito. Usa sempre AT TIME ZONE:

SELECT
  created_at AT TIME ZONE 'Europe/Rome' AS local_time,
  DATE_TRUNC('day', created_at AT TIME ZONE 'Europe/Rome') AS local_date
FROM orders;

Insidia 2: Confrontare date di fusi diversi

Se la tabella orders ha created_at in UTC e delivered_at in CET, la differenza delivered_at - created_at non è la durata reale della consegna: è falsata dalla differenza di fuso (1-2 ore a seconda dell’ora legale). Soluzione: normalizzare tutto a un fuso (tipicamente UTC) prima di calcolare differenze.

SELECT
  order_id,
  (delivered_at AT TIME ZONE 'UTC') -
  (created_at AT TIME ZONE 'UTC') AS delivery_duration
FROM orders;

Insidia 3: DATE_TRUNC alla mezzanotte sbagliata

DATE_TRUNC('day', timestamp) tronca alla mezzanotte UTC. Eventi delle 23:00 di Roma (21:00 UTC) e delle 01:00 di Roma (23:00 UTC del giorno prima) finiscono in giorni diversi, anche se per l’utente sono lo stesso giorno. La soluzione più pulita è convertire prima nel fuso target e poi truncare.

Generare serie temporali: la date spine

Un pattern fondamentale che molti analisti scoprono tardi è la date spine: una tabella (o CTE generata) con tutte le date in un intervallo, usata per garantire che ogni periodo appaia nel risultato anche se non ha dati. Senza date spine, i grafici hanno “buchi” che nascondono la reale stagionalità.

WITH date_spine AS (
  SELECT generate_series(
    '2024-01-01'::date,
    '2024-12-31'::date,
    '1 day'::interval
  )::date AS dt
)
SELECT ds.dt, COALESCE(SUM(o.amount), 0) AS daily_revenue
FROM date_spine ds
LEFT JOIN orders o ON ds.dt = o.order_date::date
GROUP BY ds.dt
ORDER BY ds.dt;

In PostgreSQL, generate_series() crea la spina. In BigQuery, si usa UNNEST(GENERATE_DATE_ARRAY(...)). In ClickHouse, una subquery con numbers() e aritmetica di date. Il concetto è lo stesso: una CTE che produce una riga per ogni data.

Caso reale: Glovo e la date spine per le metriche operative

Glovo, piattaforma di delivery attiva in 25 paesi, usa date spine per tutte le metriche operative. Una query tipica del team di analytics calcola il numero di corrieri attivi per giorno in ogni città:

WITH spine AS (
  SELECT d.dt, c.city_id
  FROM generate_series('2024-01-01', '2024-01-31', INTERVAL '1 day') d(dt)
  CROSS JOIN cities c
)
SELECT s.dt, s.city_id, COUNT(DISTINCT dc.courier_id) AS active_couriers
FROM spine s
LEFT JOIN delivery_completions dc
  ON s.dt = dc.completion_date::date AND s.city_id = dc.city_id
GROUP BY s.dt, s.city_id;

Glovo ha documentato (Glovo Engineering Blog, 2022) che la date spine ha permesso di scoprire che in 18 città su 400, il numero di corrieri attivi scendeva a zero almeno un giorno al mese — un problema di supply che senza date spine era invisibile nei grafici aggregati.

Metriche rolling: finestre temporali scorrevoli

Il passaggio successivo dopo la date spine sono le metriche rolling: medie mobili, somme cumulate su finestre di N giorni. Le window function con RANGE sono lo strumento giusto.

Rolling 7-day average:

SELECT dt, daily_revenue,
  AVG(daily_revenue) OVER (
    ORDER BY dt
    RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW
  ) AS rolling_7day_avg
FROM daily_revenue;

RANGE BETWEEN INTERVAL è cruciale: se usi ROWS 6 PRECEDING e ci sono giorni senza dati (buchi nella spina), la media è calcolata sulle ultime 7 righe, non sugli ultimi 7 giorni. Con una date spine, ROWS funziona; senza, RANGE è l’unica opzione corretta.

🖥️ Prova in Sandbox

[SQL]Prova tu

Raggruppa gli ordini per mese e calcola il revenue mensile. Usa DATE_TRUNC o la funzione substr() per estrarre il mese dalla data_ordine.

SELECT data_ordine, importo FROM ordini ORDER BY data_ordine

EseguiCtrl+Enter per eseguireSuggerimentoSoluzione

[SQL]Prova tu

Trova gli utenti che si sono registrati dopo il 1° febbraio 2025. Filtra per data e conta quanti sono.

SELECT id, nome, creato_il FROM utenti ORDER BY creato_il

EseguiCtrl+Enter per eseguireSuggerimentoSoluzione

Laboratorio pratico

Dataset: sensor_readings con timestamp e temperatura da 50 sensori per 6 mesi. Molti timestamp mancanti (sensori offline).

Livello 1 — Date spine con sensori: Crea una date spine oraria × sensore e calcola la temperatura media per ora.

Livello 2 — Rolling alert: Calcola la rolling average su 24 ore. Marca come alert le ore dove la temperatura supera di 3 deviazioni standard la rolling average delle 24 ore precedenti.

Livello 3 — Timezone locale per sensore: Ogni sensore ha una colonna timezone (es. 'Europe/Rome'). Converti tutti i timestamp nel fuso locale del sensore prima di aggregare per giorno.

Checkpoint operativi

1. Perché la date spine è necessaria e cosa succede se non la usi?

2. Qual è la differenza tra ROWS e RANGE in una finestra temporale rolling e quando RANGE è indispensabile?

3. Qual è la regola d’oro di Tom Kyte per la gestione dei fusi orari e quali insidie evita?

---

Riferimenti accademici:

• Kyte, T. (2005). Expert Oracle Database Architecture. Apress. Capitolo 12: “Datatypes.”
• Snodgrass, R. (2000). Developing Time-Oriented Database Applications in SQL. Morgan Kaufmann.
• Kuhn, D. & Kyte, T. (2014). Expert Oracle Database Architecture, 3rd ed. Apress. Capitolo 12.

Controllo di qualità

Prima di usare date-time pitfalls e timezone correctness in una decisione, controlla sempre completezza, duplicati, timezone, definizioni cambiate e segmenti esclusi. Molte analisi apparentemente sofisticate falliscono perché il dato di partenza misura un comportamento diverso da quello che il team crede di osservare.

Problema reale

Nel dominio di advanced SQL, Date-time pitfalls e timezone correctness serve a risolvere questo problema: scrivere query analitiche corrette anche quando grain, finestre, coorti e casi limite diventano complessi. La lezione non va trattata come teoria isolata, ma come un modo per migliorare una scelta concreta con dati, assunzioni esplicite e controlli minimi.

Obiettivo operativo: Comprendere il problema analitico e il contesto decisionale; Applicare esempi, metriche e controlli a casi reali. Se alla fine non sai indicare quale decisione cambia, quale dato osservi e quale errore vuoi evitare, la lezione non è ancora diventata competenza applicata.

Modello concettuale

Fase	Cosa chiarire	Output
Domanda	Quale scelta reale deve migliorare?	Decisione da prendere
Misura	Quale segnale osservabile rappresenta il problema?	Metrica o dato sorgente
Controllo	Quale baseline rende il risultato interpretabile?	Confronto credibile
Azione	Che cosa cambia dopo l’analisi?	Prossimo passo operativo

Il modello concettuale è intenzionalmente semplice: decisione, dato, controllo, azione. Ogni approfondimento tecnico deve rafforzare almeno uno di questi quattro punti.

Formalizzazione rigorosa

Per rendere Date-time pitfalls e timezone correctness analizzabile, definisci prima l’unità di lavoro: riga, partizione, finestra, join, coorte o metrica temporale. Poi collega questa unità a una metrica osservabile: correttezza, performance, duplicati, grain e stabilità del risultato. Infine dichiara la decisione attesa: query, modello intermedio, test SQL o pattern riusabile.

Elemento	Specifica richiesta
Unità di analisi	riga, partizione, finestra, join, coorte o metrica temporale
Segnale principale	correttezza, performance, duplicati, grain e stabilità del risultato
Baseline	Periodo precedente, gruppo comparabile, benchmark o scenario controfattuale
Decisione	query, modello intermedio, test SQL o pattern riusabile
Rischio	Scambiare un numero disponibile per una prova sufficiente

La formalizzazione e solida quando un altro analista può riprodurre la logica, criticare le assunzioni e ottenere la stessa decisione partendo dagli stessi dati.

Esempio o caso studio

La stessa giornata commerciale inizia a orari diversi per utenti, server e business unit. Prima di confrontare KPI giornalieri, il team deve fissare timezone, calendario fiscale, inclusione dei bordi e regole per eventi arrivati in ritardo.

Evidenza osservata	Lettura prudente	Azione consigliata
Il numero migliora	Potrebbe essere effetto reale o variazione normale	Cercare confronto e segmento
Un segmento cambia più degli altri	La media aggregata nasconde una differenza	Separare coorti o casi d’uso
Il costo cresce insieme al risultato	L’impatto va letto sul margine	Stimare trade-off e sostenibilità

Lab / esercizio

Livello base

Scrivi una scheda di una pagina per Date-time pitfalls e timezone correctness: decisione da supportare, metrica primaria, baseline, rischio principale e azione se il segnale e confermato.

Livello intermedio

Costruisci una tabella con tre segmenti, periodi o scenari. Per ciascuno indica cosa cambia, quale spiegazione alternativa e plausibile e quale controllo useresti prima di raccomandare un azione.

Livello research-grade

Prepara un decision memo: ipotesi, dati richiesti, criteri di esclusione, controlli di qualità, soglia decisionale, rischio residuo e piano di monitoraggio dopo la decisione.

Dataset e materiali consigliati

Usa warehouse SQL, dati ordini, eventi, sessioni, coorti, revenue e tabelle calendario. Se non hai accesso a dati reali, crea un dataset sintetico con almeno 200 righe, una dimensione temporale, una dimensione segmento e una metrica di outcome.

Errore tipico da evitare

L’errore più comune e usare Date-time pitfalls e timezone correctness come etichetta invece che come processo. Succede quando il team mostra un grafico senza decisione, una metrica senza baseline, o una conclusione senza indicare quale assunzione potrebbe invalidarla.

La domanda di controllo è: se questo risultato fosse instabile, quale scelta sbaglierei? Se la risposta non è concreta, manca ancora il collegamento tra analisi e azione.

Quiz o checkpoint

1. Quale decisione concreta dovrebbe migliorare questa lezione?
2. Quale unità di analisi rende il problema misurabile?
3. Quale baseline useresti per evitare una lettura ingenua?
4. Quale errore tipico potrebbe cambiare la conclusione?
5. Quale output consegneresti a uno stakeholder non tecnico?

Riepilogo operativo

Date-time pitfalls e timezone correctness diventa utile quando produce una decisione più chiara, non quando aggiunge terminologia. Usa il framework problema, modello, formalizzazione, esempio, lab e checkpoint per trasformare la lezione in pratica verificabile. Categoria: Decisione. Difficoltà: advanced. Tempo stimato: 18 min.