Lokale Tage in UTC-Daten mit SQL Server abfragen

Ein Bericht über Bestellungen vom Sonntag braucht zuerst eine klare Definition: In welcher Zeitzone beginnt dieser Sonntag? Ein UTC-Zeitstempel beschreibt einen Zeitpunkt. Das angezeigte Kalenderdatum hängt dagegen vom Standort beziehungsweise von der vereinbarten Geschäftszeitzone ab. Ein fester Zeitversatz liefert häufig plausible Ergebnisse, versagt aber an Tagen mit einer Zeitumstellung. Auch ein pauschales Addieren von 24 Stunden zum umgerechneten Tagesanfang ist dann falsch.

Eine brauchbare Konvention ist eine datetime2-Spalte mit einem ausdrücklich dokumentierten UTC-Vertrag, beispielsweise OrderedAtUtc. Der Datentyp selbst speichert keine Zeitzone und erzwingt UTC nicht. Alle schreibenden Anwendungen müssen diese Vereinbarung einhalten. datetimeoffset speichert einen Versatz, aber noch keine benannte Zeitzone mit ihren historischen Regeln.

Beide Grenzen getrennt umrechnen

Für den lokalen 10. März 2024 in Denver werden zunächst zwei lokale Mitternachtswerte gebildet. Erst danach wird jeder Wert unabhängig nach UTC umgerechnet. Der verwendete Windows-Zonenname muss auf der Instanz verfügbar sein; sys.time_zone_info zeigt die unterstützten Namen.

DECLARE @LocalDate date = '20240310';
DECLARE @Zone sysname = N'Mountain Standard Time';
DECLARE @StartLocal datetime2(0) = CONVERT(datetime2(0), @LocalDate);
DECLARE @EndLocal datetime2(0) = DATEADD(day, 1, @StartLocal);
DECLARE @StartUtc datetime2(0) = CONVERT(datetime2(0),
    @StartLocal AT TIME ZONE @Zone AT TIME ZONE 'UTC');
DECLARE @EndUtc datetime2(0) = CONVERT(datetime2(0),
    @EndLocal AT TIME ZONE @Zone AT TIME ZONE 'UTC');
SELECT @StartUtc AS StartUtc, @EndUtc AS EndUtc,
       DATEDIFF(hour, @StartUtc, @EndUtc) AS HoursInLocalDay;

Das erwartete Intervall beginnt am 10. März um 07:00 UTC und endet am 11. März um 06:00 UTC. Dieser lokale Tag hat 23 Stunden. Obwohl der Bezeichner "Standard Time" enthält, berücksichtigt er die Sommerzeit. Beim lokalen 3. November 2024 ergibt sich entsprechend ein Intervall von 25 Stunden.

Die erste Anwendung von AT TIME ZONE interpretiert den datetime2-Wert als Ortszeit der angegebenen Zone. Die zweite wandelt den entstandenen datetimeoffset-Wert in UTC um. Erst anschließend wird der nun auf null stehende Versatz entfernt, damit die Grenze zum UTC-datetime2-Vertrag der gespeicherten Spalte passt.

Historische Regeländerungen können in manchen Zonen auch Mitternacht betreffen. Bei frei eingegebenen Terminzeiten muss die Anwendung entscheiden, wie sie nicht existierende oder doppelt vorkommende Ortszeiten behandelt. Die automatische Auflösung einer Datenbankfunktion ist nicht zwingend die gewünschte Geschäftsregel. Speichern Sie bei solchen Entscheidungen auch den ursprünglichen Kontext.

Die Spalte unverändert vergleichen

Die eigentliche Abfrage verwendet die vorberechneten Grenzen direkt.

-- Assumes Orders.OrderedAtUtc contains UTC datetime2 values.
SELECT OrderId, OrderedAtUtc, Total
FROM dbo.Orders
WHERE OrderedAtUtc >= @StartUtc
  AND OrderedAtUtc < @EndUtc;

Die untere Grenze gehört zum Intervall, die obere nicht. Eine Bestellung genau um Mitternacht des Folgetags wird damit korrekt erst dort gezählt. Das Verfahren funktioniert unabhängig von der Nachkommastellengenauigkeit und benötigt keine künstliche letzte Tageszeit wie 23:59:59.997.

Ein Index mit OrderedAtUtc als führender Schlüsselspalte kann diesen Bereich unterstützen. Werden Berichte immer für genau einen Mandanten erstellt, kann TenantId vor OrderedAtUtc sinnvoll sein. Maßgeblich sind die tatsächlichen Filter und Zugriffsmuster. Wird stattdessen jede gespeicherte Zeit im WHERE-Ausdruck umgerechnet, erschwert das einen direkten Bereichszugriff und wiederholt die Konvertierung für viele Zeilen.

Die Parametertypen sollten zur Spalte passen. Bei einer datetimeoffset-Spalte sollten auch die Grenzen diesen Typ behalten. Prüfen Sie außerdem die Reihenfolge der angefragten Daten und begrenzen Sie versehentlich sehr große Berichtsintervalle.

Zeitbedeutung dauerhaft erhalten

Mischen Sie niemals UTC und lokale Serverzeiten in derselben Spalte. Nachträglich lässt sich oft nicht mehr feststellen, welche Konvention für eine Zeile galt. Besonders die im Herbst wiederholte Stunde ist ohne zusätzlichen Kontext mehrdeutig. Prüfen Sie deshalb auch Importprogramme, Standardwerte und selten genutzte Schreibwege.

Ein zukünftiger Termin um 09:00 Ortszeit unterscheidet sich von einem bereits eingetretenen Bestellereignis. Gesetzliche Zeitzonenregeln können sich ändern. Je nach Produktanforderung müssen das gewünschte lokale Datum, die lokale Uhrzeit und die benannte Zone erhalten bleiben; dazu gehört eine Regel für die spätere Berechnung des UTC-Zeitpunkts.

Testfälle sollten normale Tage, beide Zeitumstellungen, exakte Grenzwerte und verschiedene Mandantenzonen enthalten. Vergleichen Sie die ausgewählten Bestellnummern, nicht lediglich Summen. Zwei gegenläufige Fehler können dieselbe Summe erzeugen und trotzdem unterschiedliche Bestellungen betreffen. So wird der Zeitvertrag tatsächlich überprüft.

Technische Referenzen: Microsoft Learn: AT TIME ZONE · Microsoft Learn: datetime2.