Narcis Miclaus

8 min de citit

SQL Server, de la zero Lecția 15 / 40

Date și ore: mlaștina fusurilor orare

DATETIMEOFFSET, AT TIME ZONE, capcanele lui DATEDIFF, stocare în UTC vs local și oroarea specifică UE a orei de vară. De ce Runehold marchează fiecare timestamp.

Dacă există o zonă în care o bază de date din producție poate să devieze tăcut și greșit ani la rând înainte ca cineva să observe, aceea sunt datele și orele. Runehold livrează în 27 de țări UE, fiecare cu propriul fus orar. Ora de vară se schimbă de două ori pe an. Clienții din Italia plasează comenzi la 23:59 ora locală; depozitul din Polonia le vede la 23:59 ora lor locală (care poate fi aceeași sau diferită, în funcție de lună); finanțele le raportează în ora din Amsterdam; integrarea Shopify le trimite în UTC. Dacă nu te gândești la asta din start, ajungi la rapoarte unde „ieri” este decalat cu o zi pentru 30% din date.

Lecția aceasta este versiunea „realitate UE” pentru cum manipulezi timpul.

Regula de aur: stochează UTC, afișează local

Alege una: fiecare timestamp din baza ta de date este stocat în UTC. Fiecare raport orientat către utilizator convertește la fusul orar local al utilizatorului în momentul afișării. Fără excepții.

De ce: UTC nu are oră de vară, nu sare în jur și este neambiguu. Orice oră locală este o funcție de UTC + o regulă de fus orar. Drumul UTC → local este mereu bine definit. Drumul local → local sau local → UTC fără a cunoaște fusul orar al sursei nu este bine definit. Stochează valoarea neambiguuă.

Excepție: dacă un timestamp reprezintă o intenție ancorată unui loc („livrarea trebuie să ajungă la 10:00 ora din Amsterdam, indiferent dacă este CEST sau CET în acea zi”) — trebuie să stochezi atât datetime-ul, CÂT și fusul orar, ceea ce este exact rolul lui DATETIMEOFFSET. Vom acoperi asta.

Tipurile, încă o dată, pe scurt

Din lecția 4:

  • DATETIME — vechi, precizie 3,33 ms, 1753–9999. Nu folosi în cod nou.
  • DATETIME2(n) — modern, precizie până la 100 ns, 0001–9999. Implicit pentru timestamp-uri UTC.
  • DATE — doar dată, fără oră. 3 octeți.
  • TIME(n) — oră a zilei, fără dată. 3–5 octeți.
  • DATETIMEOFFSET(n) — datetime + offset față de UTC. Folosește când trebuie să păstrezi fusul orar original.

Regulă de bază: dacă coloana reprezintă „când s-a întâmplat ceva” și aplicația este sursa adevărului, folosește DATETIME2(0) și stochează UTC. Dacă coloana trebuie să păstreze fusul orar local original, folosește DATETIMEOFFSET.

SYSUTCDATETIME vs GETDATE vs GETUTCDATE

SELECT
    GETDATE()         AS server_local_now,    -- DATETIME, fusul orar local al serverului
    SYSDATETIME()     AS server_local_now_2,  -- DATETIME2, fusul orar local al serverului
    GETUTCDATE()      AS utc_now,              -- DATETIME, UTC
    SYSUTCDATETIME()  AS utc_now_2,            -- DATETIME2, UTC
    SYSDATETIMEOFFSET() AS utc_with_offset;    -- DATETIMEOFFSET, UTC cu offset

Preferă SYSUTCDATETIME() pentru valori DEFAULT și insert-uri. Este UTC, este precis, este rezistent în timp. Fiecare CreatedAt, UpdatedAt, OrderDate, ShippedAt din Runehold îl folosește:

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

Niciodată GETDATE() într-un default — dacă fusul orar al serverului tău se schimbă vreodată (migrare, mutare de server, restart de container), fiecare rând nou are default-ul în alt fus orar. UTC este stabil.

AT TIME ZONE: singurul built-in de care ai nevoie

AT TIME ZONE convertește un datetime la (sau de la) un fus orar denumit. Adăugat în SQL Server 2016. Foarte subutilizat.

-- Convertește UTC în ora locală italiană
SELECT SYSUTCDATETIME() AT TIME ZONE 'UTC' AT TIME ZONE 'W. Europe Standard Time' AS it_local;

-- Convertește UTC stocat înapoi la ora locală pentru un utilizator din 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;

Două apeluri AT TIME ZONE pentru că primul atribuie un fus orar unui datetime „naiv”, iar al doilea convertește la o țintă. O coloană DATETIMEOFFSET are deja un fus orar, deci ai nevoie doar de unul.

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

Numele fusurilor orare sunt ID-urile de fus orar Windows (de ex., 'W. Europe Standard Time', 'Central European Standard Time', 'GMT Standard Time'). Vezi sys.time_zone_info:

SELECT * FROM sys.time_zone_info ORDER BY current_utc_offset;

Toate cele ~140 de ID-uri cu offset-urile lor UTC curente. Notează-ți-le o dată într-un tabel de lookup.

Ora de vară: gestionată, dar fii atent

AT TIME ZONE știe despre ora de vară. Convertește un datetime UTC din ianuarie și unul din iulie la ora din Amsterdam și obții CET (UTC+1), respectiv CEST (UTC+2). Funcționează pur și simplu.

Capcana: nu calcula intervale de oră locală manual cu + 1 sau + 2. Asta merge jumătate de an și este greșit cealaltă jumătate. Folosește mereu AT TIME ZONE.

DATEADD și DATEDIFF

Doi cai de povară:

-- Adaugă / scade timp
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;

-- Diferența dintre două 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 numără traversările de granițe, nu timpul scurs. DATEDIFF(MONTH, '2026-01-31', '2026-02-01') returnează 1 pentru că o singură graniță de lună (1 februarie) a fost traversată, deși a trecut o singură zi. Aceeași capcană cu YEAR: DATEDIFF(YEAR, '2025-12-31', '2026-01-01') este 1 an.

Pentru „vârstă în luni complete”, expresia mai sigură:

-- Luni complete între două date
SELECT DATEDIFF(MONTH, start_date, end_date)
     - CASE WHEN DATEPART(DAY, end_date) < DATEPART(DAY, start_date) THEN 1 ELSE 0 END
FROM ...;

Pentru „vârstă în ani” (pentru consimțământ GDPR sau tier de loialitate):

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;

Sau mai simplu: calculează o dată, stochează într-o coloană calculată.

Trunchiere: doar dată, săptămână, lună

Pentru a trunchia un datetime la o dată:

-- Doar data, fără oră
SELECT CAST(OrderDate AS DATE) FROM Sales.Orders;
SELECT CONVERT(DATE, OrderDate) FROM Sales.Orders;   -- același lucru, în stilul CONVERT

La începutul lunii:

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

-- Sau modul mai scurt și prietenos cu indexul (SQL 2022+):
SELECT DATETRUNC(MONTH, OrderDate) FROM Sales.Orders;

DATETRUNC (SQL 2022+) este cel mai curat. Acceptă o unitate (DAY, WEEK, MONTH, QUARTER, YEAR) și trunchiază la ea.

Capcana SARGability, din nou

Funcțiile de trunchiere pe coloana indexată sparg indexul:

-- Rău: nu poate face seek pe indexul lui OrderDate
WHERE CAST(OrderDate AS DATE) = '2026-03-15'

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

Pentru găleți lunare:

-- Rău
WHERE YEAR(OrderDate) = 2026 AND MONTH(OrderDate) = 3

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

Intervalele semi-deschise (start inclusiv, sfârșit exclusiv) sunt modul corect de a exprima „în martie 2026”. Secunde bisecte, microsecunde, miezul nopții — niciuna nu se comportă greșit. Toate celelalte forme au cazuri-limită.

Formatare

FORMAT este cel drăguț, dar lent. CONVERT cu coduri de stil este cel rapid, dar criptic.

-- FORMAT: lizibil, lent
SELECT FORMAT(GETDATE(), 'yyyy-MM-dd HH:mm:ss')   AS iso_like;
SELECT FORMAT(GETDATE(), 'dd/MM/yyyy')            AS romanian;
SELECT FORMAT(GETDATE(), 'dddd, d MMMM yyyy', 'ro-RO') AS romanian_long;

-- CONVERT: rapid, criptic
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

Foaie de prezență pentru codurile de stil care contează:

  • 101 — SUA MM/DD/YYYY
  • 103 — European DD/MM/YYYY (corespunde cu zz/ll/aaaa)
  • 105 — Italian DD-MM-YYYY
  • 120 — ODBC YYYY-MM-DD HH:MM:SS
  • 121 — ODBC cu ms YYYY-MM-DD HH:MM:SS.mmm
  • 126 — ISO 8601 YYYY-MM-DDTHH:MM:SS.mmm

Niciodată nu formata datele în baza de date pentru răspunsuri API. Returnează datetime-ul brut (sau șirul ISO 8601) către aplicație și formatează acolo. Formatarea în SQL este doar pentru rapoarte orientate către om și, chiar și atunci, locul potrivit este adesea în unealta de raportare (Power BI, Grafana), nu în query.

ISO 8601 este prietenul tău

Pentru orice dată transmisă ca literal de string în T-SQL, folosește ISO 8601:

-- Bine: neambiguu peste tot
WHERE OrderDate >= '2026-03-15';
WHERE OrderDate >= '2026-03-15T00:00:00';
WHERE OrderDate >= '20260315';              -- tot ISO, fără separator

-- Ambiguu: interpretarea depinde de setarea de limbă
WHERE OrderDate >= '03/15/2026';            -- luna întâi? ziua întâi?
WHERE OrderDate >= '15/03/2026';

ISO 8601 este invariant cultural. '2026-03-15' înseamnă 15 martie 2026 în orice limbă pe care SQL Server o știe. Atât. Setează-l și uită de el.

Specific UE: formate de dată între limbi

Rulează asta pe serverul tău ca să vezi cum afectează SET LANGUAGE parsarea datelor:

-- English (US)
SET LANGUAGE us_english;
SELECT CAST('03/15/2026' AS DATETIME);     -- 15 martie

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

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

Schimbarea limbii schimbă valoarea implicită. De aceea ISO 8601 te salvează: independent de limbă. Nu te baza pe SET LANGUAGE să se potrivească așteptărilor utilizatorilor; convertește explicit.

Rulează asta pe propriul tău server

USE Runehold;
GO

-- 1. Ora curentă în diverse 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. Convertește datele UTC stocate la ora locală
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. Ciudățeniile de graniță ale lui 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. Venit lunar folosind DATETRUNC (2022+) vs manual
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 (vizibil în planul de execuție)
-- 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. Generează o dimensiune de date din zbor
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);

Rulează fiecare. Observă în query-ul 2 cum orele locale din Amsterdam și Berlin sunt identice (împart același fus orar), dar diferite de UTC — asta este AT TIME ZONE gestionând corect ora de vară.

Lecția următoare: CTE recursive pentru real. Organigrame, arbori de foldere, bill-of-materials, generarea de intervale de date fără un tabel-dimensiune. Construim peste ce am văzut deja în lecția 11, dar în profunzime.

Caută