What is AT TIME ZONE?
Who doesn’t know it? You’re in Seeheim Jugendheim and want to call your ninja colleague in Kyoto, but you don’t know what time it is at his place. Of course you can google the local time in Kyoto, but you can also query the time in Tokyo Standard Time in SQL Server. The tool for this is AT TIME ZONE.
How can you use AT TIME ZONE?
To answer the above question, you can simply use the following query:
SELECT GETDATE() AT TIME ZONE 'Tokyo Standard Time'
Here you select the current time and indicate that you want to know the current time in the time zone “Tokyo Standard Time”. What the SQL Server does to resolve this time zone is to rely on the system and ask the system for the time in the time zone. To do this, of course, the time zone must be resolved in your PC. You can see which time zones are available here in the registry under “KEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Time Zones”. However, you can also find a list of time zones on the net if, like us, you always get a little queasy when you open the registry editor. You can find the usually defined time zones here: https://docs.microsoft.com/en-us/windows-hardware/manufacture/desktop/default-time-zones.
Before we start with time conversions, we have to find out which time zone you have set. You can do this with the following command:
SELECT
DATEPART(TZOFFSET , SYSDATETIMEOFFSET()) as Offset,
DATENAME(TZOFFSET , SYSDATETIMEOFFSET()) as TZName
In my case I get 0 as offset and the name “+00:00”, which means that my database is set to the UTC time zone. If your test database is set to a different time zone, you may need to adjust your examples. So let’s do a conversion to German time (“W. European Standard Time”) and compare it with UTC:
SELECT
GETDATE() AT TIME ZONE 'UTC' AS [UTC]
,GETDATE() AT TIME ZONE 'W. Europe Standard Time' AS [WEST]
Here you can see one time with the attached offset “+00:00” (sure, a time in UTC has no offset to UTC time) and one with “+01:00” (if you execute this example in German wintertime) or “+02:00” (if you execute this example in German summertime).
Those of you who have converted global timestamps are aware of the pain this issue can cause, especially when you are dealing with daylight saving time and time changeover. In the year 2020, the clock in Germany was moved forward on March 29 from 2:00 to 3:00 one hour to daylight savings time. That means at 2 o’clock we go from UTC +1 hour to UTC +2 hours. The SQL Server then shifts the times in this interval to the time after the changeover. Let’s try this out:
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]
We see in the output that the first converted time is still UTC +1 hour, while at 2 o’clock sharp the converted time is no longer UTC +1 hour, but jumps to UTC +2 hours.
Similarly, if we jump from UTC +2 hours to UTC +1 hour, times from the overlapping interval are then shifted to times before the one before the changeover:
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]
Another thing you should know is that you can use AT TIME ZONE to query temporal tables, which means that if you have a table whose changes are versioned by the system, you can query the state of the table at a given time. For the historicized ColdRoomTemperaturesof the WideWorldImporters database, you could do this as follows:
SELECT TOP (1000) [ColdRoomTemperatureID]
,[ColdRoomSensorNumber]
,[RecordedWhen]
,[Temperature]
FROM [WideWorldImporters].[Warehouse].[ColdRoomTemperatures]
FOR SYSTEM_TIME AS OF '2016-01-01 13:00:00'
But if you now know that your cold store is in Kyoto, but the timestamps are recorded in UTC, you would first have to store the timestamp you are looking for in a variable and then execute the AS OF query with this variable:
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
Timestamps and their conversion won’t be a walk in the park with AT TIME ZONE and still causes headaches like a blow on the head with the flat side of the katana, but AT TIME ZONE at least saves you from implementing wild time zone conversions yourself. So this functionality might make life a bit more bearable if you already have to deal with time zones and have a SQL server available in a 2016 version…
