Date-time pitfalls e timezone correctness

Interpretare date e orari in analisi non è un problema solo tecnico, è una decisione presa sotto incertezza. Quando un dashboard mostra un calo alle 00:00 UTC e i team in Europa e in America leggono dati diversi, non è un errore di arrotondamento: è una scelta temporale rimasta implicita. Per decidere in modo affidabile, calendario, timestamp e giornata commerciale devono parlare la stessa lingua.

Quando il fuso decide al posto tuo

Nel SQL avanzato il problema è scrivere query corrette anche quando grain, finestre, coorti e casi limite complicano la lettura dei dati. Senza una regola chiara su come trattare date e fusi orari, le metriche diventano fuorvianti. La sfida è trasformare questa complessità in una scelta consapevole, con assunzioni e controlli dichiarati invece che dati per scontati.

Una mappa per orientarsi

Ogni approfondimento tecnico regge solo se punta a migliorare una decisione. Questo schema tiene insieme i quattro punti che lo rendono utile.

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

Definire l’unità di lavoro

Per rendere il problema analizzabile, definisci l’unità di lavoro, che sia riga, partizione, finestra, join, coorte o metrica temporale, e collegala a una metrica osservabile: correttezza, performance, presenza di duplicati, grain, stabilità. Poi dichiara la decisione attesa, da una query a un modello a un test SQL, e il rischio, che resta sempre lo stesso, scambiare un numero disponibile per una prova sufficiente.

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

Una giornata commerciale, tre orologi diversi

Considera una giornata commerciale che inizia a orari diversi per utenti, server e business unit. Prima di confrontare KPI giornalieri, il team deve fissare il timezone, il calendario fiscale, l’inclusione dei bordi e le regole per gli eventi arrivati in ritardo. Senza questi accordi, lo stesso ordine cade in giorni diversi a seconda di chi guarda.

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à

Tipi di dato temporali: una mappa minima

Per orientarsi conviene distinguere tre tipi fondamentali di dato temporale.

Tipo	Contiene	Quando usarlo
TIMESTAMP / DATETIME	Data + ora + (opzionalmente) timezone	Eventi puntuali con fuso orario noto
DATE	Solo data, nessuna ora	Aggregazioni giornaliere, compleanni, scadenze
INTERVAL	Durata tra due timestamp	Calcolo di delta, scadenze, SLA

La regola d’oro di Tom Kyte è semplice: salva sempre in UTC e converti in locale solo nel livello di presentazione. Ignorarla porta a errori che poi diventano difficili da diagnosticare.

Il problema delle timezone: tre insidie classiche

La prima insidia è assumere che il server sia nel tuo fuso. Il server può stare in UTC o in un fuso diverso, e usare CURRENT_DATE senza specificare il fuso restituisce date sbagliate. La soluzione è esplicitare sempre il fuso con AT TIME ZONE.

La seconda è confrontare date di fusi diversi. Se i timestamp in colonne diverse hanno fusi diversi, la loro differenza non rappresenta la durata reale. Conviene normalizzare tutto in UTC prima di calcolare qualunque delta.

La terza è il DATE_TRUNC alla mezzanotte sbagliata. DATE_TRUNC('day', timestamp) tronca alla mezzanotte UTC, non a quella locale, così eventi dello stesso giorno locale finiscono in giorni diversi. La soluzione è convertire prima nel fuso locale e poi troncare.

Generare serie temporali: la date spine

Una pratica fondamentale è la date spine, una tabella o CTE che contiene tutte le date di un intervallo. Garantisce che ogni periodo compaia nel risultato anche quando non ha dati, ed evita i buchi nei grafici che confondono la lettura.

Ecco un esempio in PostgreSQL:

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;

Glovo ha usato la date spine per far emergere problemi di supply che senza questa tecnica restavano invisibili, un esempio concreto di quanto valga in termini di decisione.

Metriche rolling: finestre temporali scorrevoli

Le metriche rolling, come medie mobili e somme cumulate, servono a cogliere trend e anomalie. Le window function con RANGE diventano indispensabili quando la serie temporale ha dei buchi.

Ecco un 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;

Usare ROWS al posto di RANGE calcola la media su righe invece che su giorni, e distorce il risultato ogni volta che mancano dei dati.

Un laboratorio sui dati dei sensori

Parti da una tabella sensor_readings con timestamp e temperatura di 50 sensori per 6 mesi, con molti timestamp mancanti. Al livello base costruisci una date spine oraria per sensore e calcola la temperatura media per ora. Al livello intermedio calcola la rolling average su 24 ore e marca come alert le ore in cui la temperatura supera di 3 deviazioni standard la media delle 24 ore precedenti. Al livello più avanzato ogni sensore ha una colonna timezone: converti i timestamp nel fuso locale prima di aggregare per giorno.

Costruire l’analisi a tre profondità

Lo stesso lavoro si può inquadrare anche fuori dal dataset dei sensori. Comincia scrivendo la decisione che la lezione dovrebbe migliorare, la metrica principale e il rischio da controllare. Poi costruisci una tabella con baseline, segnale, interpretazione prudente e azione consigliata. Infine trasforma l’esercizio in un memo decisionale con assunzioni, limiti, criterio di stop e controllo successivo. Come materiale va bene un export reale, un dataset sintetico o una dashboard già esistente, purché contenga una domanda, una metrica e una scelta da prendere.

L’errore che svuota l’analisi

Il rischio più grande è usare “date-time pitfalls e timezone correctness” come etichetta senza processo: grafici senza decisione, metriche senza baseline, conclusioni senza assunzioni dichiarate. Se non sai quale scelta sbaglieresti nel caso i dati fossero instabili, manca il collegamento tra analisi e azione.

Verifica di comprensione

Per controllare la presa, prova a rispondere. Quale decisione concreta dovrebbe migliorare questa lezione? Quale unità di analisi rende il problema misurabile? Quale baseline useresti per evitare una lettura ingenua? Quale errore tipico potrebbe cambiare la conclusione? E quale output consegneresti a uno stakeholder non tecnico?

Riepilogo operativo

Gestire date, orari e fusi in SQL con disciplina è ciò che rende affidabili le decisioni. Date spine, conversioni di timezone esplicite e rolling metric con window function trasformano dati incerti in segnali leggibili. La lezione vale qualcosa solo se produce una decisione più chiara, non solo terminologia tecnica.

---

[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