Date-time pitfalls and 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.

Phase	What to clarify	Output
Question	Which real choice needs improvement?	Decision to make
Measure	Which observable signal represents the problem?	Metric or source data
Control	Which baseline makes the result interpretable?	Credible comparison
Action	What changes after the analysis?	Next operational step

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.

Element	Requested specification
Unit of analysis	row, partition, window, join, cohort, or temporal metric
Primary signal	correctness, performance, duplicates, grain, and result stability
Baseline	Previous period, comparable group, benchmark, or counterfactual scenario
Decision	query, intermediate model, SQL test, or reusable pattern
Risk	Mistaking an available number for sufficient proof

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.

Observed evidence	Cautious interpretation	Recommended action
The number improves	Could be a real effect or normal variation	Look for comparison and segment
One segment changes more than others	The aggregated average hides a difference	Separate cohorts or use cases
Cost grows along with the result	Impact must be read on the margin	Estimate trade-offs and sustainability

Temporal data types: a minimal map

Per orientarsi conviene distinguere tre tipi fondamentali di dato temporale.

Type	Contains	When to Use
TIMESTAMP / DATETIME	Date + time + (optionally) timezone	Point events with known timezone
DATE	Date only, no time	Daily aggregations, birthdays, deadlines
INTERVAL	Duration between two timestamps	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.

The timezone problem: three classic pitfalls

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.

Generating time series: the 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.

Rolling metrics: sliding time windows

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 instead of 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?

Operational Summary

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]Try it yourself

Group orders by month and calculate monthly revenue. Use DATE_TRUNC or the substr() function to extract the month from order_date.

SELECT data_ordine, importo FROM ordini ORDER BY data_ordine

RunCtrl+Enter to runHintSolution

[SQL]Try it yourself

Find users who registered after February 1, 2025. Filter by date and count how many there are.

SELECT id, nome, creato_il FROM utenti ORDER BY creato_il

RunCtrl+Enter to runHintSolution