Was ist AT TIME ZONE?
Wer kennt das nicht? Ihr seid in Seeheim Jugendheim und wollt euren Ninja-Kollegen in Kyoto anrufen, wisst aber gar nicht, wie spät es bei ihm gerade ist. Ihr könnt die lokale Zeit in Kyoto natürlich googeln, allerdings könnt ihr die Zeit in Tokyo Standard Time auch im SQL Server abfragen. Das Hilfsmittel hierfür ist AT TIME ZONE.
Wie könnt ihr AT TIME ZONE verwenden?
Um die obige Frage zu beantwortet, könnt ihr einfach folgende Abfrage verwenden:
SELECT GETDATE() AT TIME ZONE 'Tokyo Standard Time'
Hier wählt ihr die aktuelle Zeit aus und gebt an, dass ihr die aktuelle Zeit in der Zeitzone “Tokyo Standard Time” erfahren möchtet. Was der SQL Server macht, um diese Zeitzone aufzulösen ist, sich auf das System zu verlassen und vom System die Zeit in der Zeitzone abzufragen. Dafür muss die Zeitzone natürlich in eurem PC aufgelöst werden. Welche Zeitzonen euch hier zur Verfügung stehen, könnt ihr in der Registry einsehen unter “KEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Time Zones”. Ihr könnt jedoch auch eine List der Zeitzonen im Netz finden, wenn euch wie uns beim Öffnen des Registry-Editors immer ein wenig flau im Magen wird. Die üblicherweise definierten Zeitzonen könnt ihr hier finden: https://docs.microsoft.com/en-us/windows-hardware/manufacture/desktop/default-time-zones.
Bevor wir mit Zeitumrechnungen beginnen, müssen wir herausfinden, welche Zeitzone bei euch eingestellt ist. Das könnt ihr über folgendes Kommando tun:
SELECT
DATEPART(TZOFFSET , SYSDATETIMEOFFSET()) as Offset,
DATENAME(TZOFFSET , SYSDATETIMEOFFSET()) as TZName
In meinem Fall erhalte ich 0 als Offset und den Namen “+00:00”, was bedeutet, dass auf meiner Datenbank die UTC-Zeitzone eingestellt ist. Sollte auf eurer Test-Datenbank eine andere Zeitzone eingestellt sein, müsst ihr eure Beispiele gegebenenfalls anpassen. Führen wir nun also eine Konversion nach deutscher Zeit aus (“W. European Standard Time”) und vergleichen mit UTC:
SELECT
GETDATE() AT TIME ZONE 'UTC' AS [UTC]
,GETDATE() AT TIME ZONE 'W. Europe Standard Time' AS [WEST]
Hier seht ihr nun bei UTC (wenn eure Zeit auf dem Server so eingestellt ist wie im Dojo) eine Zeit mit dem angehängten Offset “+00:00” (klar, eine Zeitangabe in UTC hat keinen Offset zur UTC-Zeit) und eine mit “+01:00” (wenn ihr dieses Beispiel in der deutschen Winterzeit ausführt) bzw. “+02:00” (wenn ihr dieses Beispiel in der deutschen Sommerzeit ausführt).
Wer nun von euch einmal globale Zeitstempel umgewandelt hat, dem ist der Schmerz bewusst, den diese Thematik verursachen kann, insbesondere wenn mit Sommerzeit und Zeitumstellung hantiert wird. Jahr 2020 wurde in Deutschland die Uhr am 29. März von 2:00 auf 3:00 eine Stunde auf die Sommerzeit vorgestellt. Das bedeutet um 2 Uhr gehen wir von UTC +1 Stunde auf UTC +2 Stunden. Der SQL Server schiebt die Zeiten in diesem Intervall dann auf die Zeit nach der Umstellung. Probieren wir das aus:
SELECT
CONVERT(datetime, '2020-03-29T01:59:00')
AT TIME ZONE 'UTC' AS [UTC]
,CONVERT(datetime, '2020-03-29T01:59:00')
AT TIME ZONE 'W. Europe Standard Time' AS [WEST]
UNION ALL
SELECT
CONVERT(datetime, '2020-03-29T02:00:00')
AT TIME ZONE 'UTC' AS [UTC]
,CONVERT(datetime, '2020-03-29T02:00:00')
AT TIME ZONE 'W. Europe Standard Time' AS [WEST]
UNION ALL
SELECT
CONVERT(datetime, '2020-03-29T02:01:00')
AT TIME ZONE 'UTC' AS [UTC]
,CONVERT(datetime, '2020-03-29T02:01:00')
AT TIME ZONE 'W. Europe Standard Time' AS [WEST]
Wir sehen in der Ausgabe, dass die erste umgewandelte Zeit noch UTC +1 Stunde ist, während um Punkt 2 Uhr die umgewandelte Zeit nicht mehr UTC +1 Stunde ist, sondern auf UTC +2 Stunden springt.
Ähnlich ist das mit dem “Rückweg”, wenn wir von UTC +2 Stunden auf UTC +1 Stunde springen, Zeiten aus dem überlappenden Intervall werden dann auf Zeiten vor der vor der Umstellung geschoben:
SELECT
CONVERT(datetime, '2020-10-25T02:59:00')
AT TIME ZONE 'UTC' AS [UTC]
,CONVERT(datetime, '2020-10-25T02:59:00')
AT TIME ZONE 'W. Europe Standard Time' AS [WEST]
UNION ALL
SELECT
CONVERT(datetime, '2020-10-25T03:00:00')
AT TIME ZONE 'UTC' AS [UTC]
,CONVERT(datetime, '2020-10-25T03:00:00')
AT TIME ZONE 'W. Europe Standard Time' AS [WEST]
UNION ALL
SELECT
CONVERT(datetime, '2020-10-25T03:01:00')
AT TIME ZONE 'UTC' AS [UTC]
,CONVERT(datetime, '2020-10-25T03:01:00')
AT TIME ZONE 'W. Europe Standard Time' AS [WEST]
Was ihr sonst noch wissen solltet: ihr könnt mit AT TIME ZONE auch temporale Tabellen abfragen, das heißt wenn ihr eine Tabelle habt, deren Änderungen vom System versioniert werden, dann könnt ihr den Zustand der Tabelle zu einem bestimmten Zeitpunkt abfragen. Für die historisierten ColdRoomTemperatures der WideWorldImporters Datenbank könntet ihr das wie folgt tun:
SELECT TOP (1000) [ColdRoomTemperatureID]
,[ColdRoomSensorNumber]
,[RecordedWhen]
,[Temperature]
FROM [WideWorldImporters].[Warehouse].[ColdRoomTemperatures]
FOR SYSTEM_TIME AS OF '2016-01-01 13:00:00'
Wenn ihr nun aber wisst, dass euer Kühlraum in Kyoto steht, die Zeitstempel aber in UTC aufgezeichnet werden, müsstet ihr zunächst den gesuchten Zeitstempel in eine Variable abspeichern und die AS OF Abfrage dann mit dieser Variablen ausführen:
DECLARE @asof datetimeoffset
= CONVERT(datetime, '2016-01-01 13:00:00')
AT TIME ZONE 'Tokyo Standard Time'
SELECT TOP (1000) [ColdRoomTemperatureID]
,[ColdRoomSensorNumber]
,[RecordedWhen]
,[Temperature]
FROM [WideWorldImporters].[Warehouse].[ColdRoomTemperatures]
FOR SYSTEM_TIME AS OF @asof
Zeitstempel und deren Umrechnung wird zwar auch mit AT TIME ZONE kein Zuckerschlecken und bereitet immer noch Kopfschmerzen wie ein Schlag auf den Kopf mit der flachen Seite des Katana, aber AT TIME ZONE erspart es euch wenigstens, wilde Zeitzonen-Umrechnungen selbst zu implementieren. Insofern macht diese Funktionalität euch das Leben möglicherweise ein bisschen erträglicher, wenn ihr schon mit Zeitzonen hantieren müsst und einen SQL Server in einer Version ab 2016 zur Verfügung habt…
