Narcis Miclaus

8 min di lettura

SQL Server, dalle fondamenta Lezione 15 / 40

Date e orari: la palude dei timezone

DATETIMEOFFSET, AT TIME ZONE, le trappole di DATEDIFF, salvare UTC vs locale, e l'orrore EU-specifico dell'ora legale. Perché Runehold etichetta ogni timestamp.

Se c’è un’area in cui un database di produzione può silenziosamente derivare nel torto per anni prima che qualcuno se ne accorga, sono date e orari. Runehold spedisce in 27 paesi UE, ognuno con il suo fuso. L’ora legale cambia due volte l’anno. I clienti in Italia piazzano ordini alle 23:59 ora locale; il magazzino in Polonia li vede alle 23:59 ora locale loro (che potrebbe essere la stessa o diversa a seconda del mese); il finance li riporta in ora di Amsterdam; l’integrazione Shopify li manda in UTC. Se non ci pensi a monte, finisci con report dove “ieri” è sfasato di un giorno per il 30% dei tuoi dati.

Questa lezione è la versione realtà-UE di come gestire il tempo.

La regola d’oro: salva UTC, mostra in locale

Scegline una: ogni timestamp nel tuo database è salvato in UTC. Ogni report rivolto all’utente converte nel fuso orario locale dell’utente al momento della visualizzazione. Nessuna eccezione.

Perché: UTC non ha ora legale, non si sposta, ed è univoco. Qualsiasi ora locale è una funzione di UTC + una regola di fuso. Andare da UTC a locale è sempre ben definito. Andare da locale a locale o da locale a UTC senza sapere il fuso di partenza non è ben definito. Salva quello univoco.

Eccezione: se un timestamp rappresenta un’intenzione ancorata a un luogo (“la consegna deve arrivare alle 10:00 ora di Amsterdam, indipendentemente che quel giorno sia CEST o CET”) — devi salvare sia il datetime SIA il fuso, ed è esattamente per questo che esiste DATETIMEOFFSET. Lo vedremo.

I tipi, di nuovo, in breve

Dalla lezione 4:

  • DATETIME — vecchio, precisione 3.33ms, 1753–9999. Non usarlo nel codice nuovo.
  • DATETIME2(n) — moderno, fino a 100ns di precisione, 0001–9999. Default per timestamp UTC.
  • DATE — solo data, niente ora. 3 byte.
  • TIME(n) — solo ora del giorno, niente data. 3–5 byte.
  • DATETIMEOFFSET(n) — datetime + offset da UTC. Usalo quando devi preservare il fuso originale.

Regola pratica: se la colonna rappresenta “quando è successo qualcosa” e l’app è la fonte di verità, usa DATETIME2(0) e salva UTC. Se la colonna deve preservare il fuso originale locale, usa DATETIMEOFFSET.

SYSUTCDATETIME vs GETDATE vs GETUTCDATE

SELECT
    GETDATE()         AS server_local_now,    -- DATETIME, fuso locale del server
    SYSDATETIME()     AS server_local_now_2,  -- DATETIME2, fuso locale del server
    GETUTCDATE()      AS utc_now,              -- DATETIME, UTC
    SYSUTCDATETIME()  AS utc_now_2,            -- DATETIME2, UTC
    SYSDATETIMEOFFSET() AS utc_with_offset;    -- DATETIMEOFFSET, UTC con offset

Preferisci SYSUTCDATETIME() per i valori DEFAULT e gli insert. È UTC, è preciso, è a prova di futuro. Ogni CreatedAt, UpdatedAt, OrderDate, ShippedAt in Runehold lo usa:

CREATE TABLE Sales.Orders (
    OrderId   BIGINT IDENTITY(1,1) PRIMARY KEY,
    OrderDate DATETIME2(0) NOT NULL DEFAULT SYSUTCDATETIME(),
    ...
);

Mai GETDATE() in un default — se il fuso del tuo server cambia mai (migrazione, spostamento server, restart container), ogni riga prende come default un fuso diverso. UTC è stabile.

AT TIME ZONE: l’unica built-in che ti serve

AT TIME ZONE converte un datetime verso (o da) un fuso nominato. Aggiunto in SQL Server 2016. Enormemente sottoutilizzato.

-- Converti UTC in ora locale italiana
SELECT SYSUTCDATETIME() AT TIME ZONE 'UTC' AT TIME ZONE 'W. Europe Standard Time' AS it_local;

-- Converti UTC salvato in locale per un utente ad Amsterdam
SELECT o.OrderId,
       o.OrderDate                                                 AS utc_date,
       o.OrderDate AT TIME ZONE 'UTC' AT TIME ZONE 'W. Europe Standard Time' AS amsterdam_local
FROM Sales.Orders AS o;

Due chiamate AT TIME ZONE perché la prima assegna un fuso a un datetime “ingenuo”, e la seconda converte a un fuso target. Una colonna DATETIMEOFFSET ha già un fuso, quindi te ne serve solo una.

-- Se OrderDateOffset è DATETIMEOFFSET
SELECT o.OrderDateOffset AT TIME ZONE 'W. Europe Standard Time' AS amsterdam_local
FROM Sales.Orders AS o;

I nomi dei fusi sono gli ID di fuso di Windows (per esempio 'W. Europe Standard Time', 'Central European Standard Time', 'GMT Standard Time'). Vedi sys.time_zone_info:

SELECT * FROM sys.time_zone_info ORDER BY current_utc_offset;

Tutti i circa 140 ID con i loro offset UTC attuali. Annotali una volta in una tabella di lookup.

Ora legale: gestita, ma stai attento

AT TIME ZONE conosce l’ora legale. Converti un datetime UTC di gennaio e uno di luglio in ora di Amsterdam e ottieni rispettivamente CET (UTC+1) e CEST (UTC+2). Funziona e basta.

La trappola: non calcolare gli intervalli in ora locale a mano con + 1 o + 2. Funziona per metà dell’anno e sbaglia per l’altra metà. Usa sempre AT TIME ZONE.

DATEADD e DATEDIFF

Due cavalli di battaglia:

-- Aggiungi / sottrai tempo
SELECT DATEADD(DAY,    -7,  GETDATE()) AS one_week_ago;
SELECT DATEADD(MONTH,  -1,  GETDATE()) AS one_month_ago;
SELECT DATEADD(HOUR,   -24, GETDATE()) AS yesterday_same_time;

-- Differenza tra due date
SELECT DATEDIFF(DAY,   '2026-01-01', '2026-04-17') AS days;        -- 106
SELECT DATEDIFF(HOUR,  '2026-01-01 00:00', '2026-01-01 15:30') AS hours;  -- 15
SELECT DATEDIFF(MONTH, '2026-01-31', '2026-02-01') AS months;       -- 1 (!!)

DATEDIFF conta gli attraversamenti di confine, non il tempo trascorso. DATEDIFF(MONTH, '2026-01-31', '2026-02-01') restituisce 1 perché è stato attraversato un confine di mese (1 febbraio), anche se è passato solo 1 giorno. Stessa trappola con YEAR: DATEDIFF(YEAR, '2025-12-31', '2026-01-01') è 1 anno.

Per “età in mesi completi”, l’idioma più sicuro:

-- Mesi completati tra due date
SELECT DATEDIFF(MONTH, start_date, end_date)
     - CASE WHEN DATEPART(DAY, end_date) < DATEPART(DAY, start_date) THEN 1 ELSE 0 END
FROM ...;

Per “età in anni” (per consenso GDPR o tier fedeltà):

SELECT DATEDIFF(YEAR, BirthDate, GETDATE())
     - CASE WHEN (DATEPART(MONTH, GETDATE()) < DATEPART(MONTH, BirthDate))
            OR (DATEPART(MONTH, GETDATE()) = DATEPART(MONTH, BirthDate)
                AND DATEPART(DAY, GETDATE()) < DATEPART(DAY, BirthDate))
            THEN 1 ELSE 0 END AS age;

O più facile: calcolala una volta, salvala in una colonna calcolata.

Troncamento: solo data, settimana, mese

Per troncare un datetime a una data:

-- Solo la data, niente ora
SELECT CAST(OrderDate AS DATE) FROM Sales.Orders;
SELECT CONVERT(DATE, OrderDate) FROM Sales.Orders;   -- stessa cosa, stile CONVERT

All’inizio del mese:

SELECT DATEFROMPARTS(YEAR(OrderDate), MONTH(OrderDate), 1) AS month_start
FROM Sales.Orders;

-- O il modo più corto e index-friendly (SQL 2022+):
SELECT DATETRUNC(MONTH, OrderDate) FROM Sales.Orders;

DATETRUNC (SQL 2022+) è il più pulito. Prende un’unità (DAY, WEEK, MONTH, QUARTER, YEAR) e tronca a quella.

La trappola della SARGability, ancora

Le funzioni di troncamento sulla colonna indicizzata rompono l’indice:

-- Male: non può fare seek sull'indice di OrderDate
WHERE CAST(OrderDate AS DATE) = '2026-03-15'

-- Bene: range query, seekable
WHERE OrderDate >= '2026-03-15' AND OrderDate < '2026-03-16'

Per i bucket mensili:

-- Male
WHERE YEAR(OrderDate) = 2026 AND MONTH(OrderDate) = 3

-- Bene
WHERE OrderDate >= '2026-03-01' AND OrderDate < '2026-04-01'

Gli intervalli semi-aperti (inizio inclusivo, fine esclusiva) sono il modo giusto di esprimere “in marzo 2026”. Secondi intercalari, microsecondi, mezzanotte — nessuno di questi si comporta male. Ogni altra forma ha casi limite.

Formattazione

FORMAT è quello carino ma lento. CONVERT con i codici di stile è quello veloce ma criptico.

-- FORMAT: leggibile, lento
SELECT FORMAT(GETDATE(), 'yyyy-MM-dd HH:mm:ss')   AS iso_like;
SELECT FORMAT(GETDATE(), 'dd/MM/yyyy')            AS italian;
SELECT FORMAT(GETDATE(), 'dddd, d MMMM yyyy', 'it-IT') AS italian_long;

-- CONVERT: veloce, criptico
SELECT CONVERT(VARCHAR(20), GETDATE(), 121)       AS iso_like;     -- 2026-04-17 10:30:45.000
SELECT CONVERT(VARCHAR(10), GETDATE(), 103)       AS euro_short;   -- 17/04/2026

Cheat sheet per i codici di stile che contano:

  • 101 — USA MM/DD/YYYY
  • 103 — Europeo DD/MM/YYYY
  • 105 — Italiano DD-MM-YYYY
  • 120 — ODBC YYYY-MM-DD HH:MM:SS
  • 121 — ODBC con ms YYYY-MM-DD HH:MM:SS.mmm
  • 126 — ISO 8601 YYYY-MM-DDTHH:MM:SS.mmm

Mai formattare le date nel database per le risposte API. Restituisci il datetime grezzo (o una stringa ISO-8601) all’app e formatta lì. Formattare in SQL è solo per i report rivolti agli umani, e anche allora spesso appartiene allo strumento di reporting (Power BI, Grafana) piuttosto che alla query.

ISO 8601 è tuo amico

Per qualsiasi data passata come stringa letterale in T-SQL, usa ISO 8601:

-- Bene: univoco ovunque
WHERE OrderDate >= '2026-03-15';
WHERE OrderDate >= '2026-03-15T00:00:00';
WHERE OrderDate >= '20260315';              -- anche ISO, senza separatore

-- Ambiguo: l'interpretazione dipende dall'impostazione di lingua
WHERE OrderDate >= '03/15/2026';            -- mese prima? giorno prima?
WHERE OrderDate >= '15/03/2026';

ISO 8601 è invariante rispetto alla cultura. '2026-03-15' significa 15 marzo 2026 in ogni lingua che SQL Server conosce. Punto. Imposta una volta e dimenticalo.

EU-specifico: formati di data tra le lingue

Esegui questo sul tuo server per vedere come SET LANGUAGE influenza il parsing delle date:

-- Inglese (USA)
SET LANGUAGE us_english;
SELECT CAST('03/15/2026' AS DATETIME);     -- 15 marzo

-- Italiano
SET LANGUAGE Italian;
SELECT CAST('15/03/2026' AS DATETIME);     -- 15 marzo

-- Britannico
SET LANGUAGE British;
SELECT CAST('15/03/2026' AS DATETIME);     -- 15 marzo

Cambiare lingua cambia il default. Ecco perché ISO 8601 ti salva: indipendente dalla lingua. Non affidarti a SET LANGUAGE per matchare le aspettative dei tuoi utenti; converti esplicitamente.

Esegui questo sulla tua macchina

USE Runehold;
GO

-- 1. Ora attuale in varie forme
SELECT
    GETDATE()           AS local_now,
    SYSDATETIME()       AS local_now_precise,
    GETUTCDATE()        AS utc_now,
    SYSUTCDATETIME()    AS utc_now_precise,
    SYSDATETIMEOFFSET() AS utc_with_offset;

-- 2. Converti date UTC salvate in ora locale
SELECT o.OrderId,
       o.OrderDate AS utc_date,
       o.OrderDate AT TIME ZONE 'UTC'
         AT TIME ZONE 'W. Europe Standard Time' AS amsterdam_local,
       o.OrderDate AT TIME ZONE 'UTC'
         AT TIME ZONE 'Central European Standard Time' AS berlin_local
FROM Sales.Orders AS o;

-- 3. Stranezze di confine in DATEDIFF
SELECT DATEDIFF(MONTH, '2026-01-31', '2026-02-01') AS misleading_months,  -- 1
       DATEDIFF(DAY,   '2026-01-31', '2026-02-01') AS real_days;          -- 1

-- 4. Fatturato mensile usando DATETRUNC (2022+) vs manuale
SELECT DATETRUNC(MONTH, OrderDate) AS month_start,
       SUM(Total) AS revenue
FROM Sales.Orders
GROUP BY DATETRUNC(MONTH, OrderDate)
ORDER BY month_start;

-- 5. SARGable vs non-SARGable (visibile nel piano di esecuzione)
-- Non-SARGable:
SELECT * FROM Sales.Orders WHERE YEAR(OrderDate) = 2026;

-- SARGable:
SELECT * FROM Sales.Orders
WHERE OrderDate >= '2026-01-01' AND OrderDate < '2027-01-01';

-- 6. Genera al volo una dimensione data
WITH dates AS (
    SELECT CAST('2026-01-01' AS DATE) AS d
    UNION ALL
    SELECT DATEADD(DAY, 1, d) FROM dates WHERE d < '2026-01-31'
)
SELECT d,
       DATENAME(WEEKDAY, d) AS day_name,
       DATEPART(ISO_WEEK, d) AS iso_week
FROM dates
OPTION (MAXRECURSION 500);

Esegui ognuna. Nota nella query 2 come le ore locali di Amsterdam e Berlino sono identiche (condividono un fuso) ma diverse da UTC — è AT TIME ZONE che gestisce correttamente l’ora legale.

Prossima lezione: CTE ricorsive sul serio. Organigrammi, alberi di cartelle, distinta base, generazione di intervalli di date senza una dimensione data. Costruendo su quello che abbiamo già visto nella lezione 11 ma andando in profondità.

Cerca