THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Louis Davidson

Using AT TIME ZONE to manipulate and convert time zones

I have a series of blogs about temporal tables in SQL Server 2016, which you can see here. However, one thing I never had mentioned was that the Temporal Row Start and End Times are always stored in UTC times (which is generally inconvenient to work with unless you are from the Great Britain area).  However, when you are looking at your data and want to see what the data looked like at 'Yesterday at 12:00 at the computer you are working from, this can be a bit more work.

In 2016 SQL Server, a new feature was added that totally passed me by. AT TIME ZONE. It is used to manipulate the time zone offset of a point in time value,either to append the time zone to a value, or to shift the time zone value of a DATETIMEOFFSET value. The most common practical use that I will show is converting a value in your local time to UTC (and vice versa).

Appending the time zone offset to a time value

The first use of AT TIME ZONE doesn't seem overly interesting for normal day to day use on the face of things. It adds time zone information to a variable. For example, consider the following point in time value:

SELECT  SYSDATETIME() AS CurrentTime;

CurrentTime
---------------------------
2017-09-01 20:40:58.5931246

Now, if you want to state that the time zone offset of a time value is for a given time zone, you can use AT TIME ZONE to append the offset (with the output type in this case being DATETIMEOFFSET(7)):

SELECT  SYSDATETIME() AT TIME ZONE 'Central Standard Time' AS CentralTime, 
SELECT  SYSDATETIME() AT TIME ZONE 'Eastern Standard Time' AS EasternTime; 

CentralTime                        EasternTime
---------------------------------- ----------------------------------

2017-09-01 20:41:28.9633676 -05:00
2017-09-01 20:41:28.9893637 -04:00

The time zone can be not only be a literal, but can be an expression, so the following will work too:

DECLARE @TimeZone1 NVARCHAR(200) = 'Central Standard Time'
SELECT  SYSDATETIME() AT TIME ZONE @TimeZone1 ;
 

This in and of itself is pretty cool. However, most of the time, I don't see many people using columns of DATETIMEOFFSET, just one of the typical point in time types like DATETIME2. Just adding the time zone is cool, but when you cast the values to a DATETIME2, the time zone is lost:

DECLARE @timeValue datetime2 = SYSDATETIME(); --fetch a point in time

       --cast the time in the Central Time Zone to datetime2 
SELECT CAST(@timeValue AT TIME ZONE 'Central Standard Time' AS datetime2) AS CurrentTime,

       --cast the time in the Pacific Time Zone to datetime2
       CAST(@timeValue AT TIME ZONE 'Pacific Standard Time' AS datetime2) AS StillCurrentTime,

       --compare the two
       CASE WHEN CAST(@timeValue AT TIME ZONE 'Central Standard Time' AS datetime2) =
               CAST(@timeValue AT TIME ZONE 'Pacific Standard Time' AS datetime2) THEN 'Match'

       ELSE 'No Match' END AS Match;

Which returns:

CurrentTime                 StillCurrentTime            Match
--------------------------- --------------------------- --------
2017-09-01 21:16:20.1161830 2017-09-01 21:16:20.1161830 Match

The offset times before you cast to the datetime2 would not match, if you compared them. For example:

DECLARE @datetimeoffset1 datetimeoffset = GETDATE() AT TIME ZONE 'Eastern Standard Time'
DECLARE @datetimeoffset2 datetimeoffset = GETDATE() AT TIME ZONE 'Central Standard Time'

SELECT CASE WHEN @datetimeoffset1 < @datetimeoffset2 THEN 1 ELSE 0 end

will return 1, because the same clock time in the Eastern Time Zone is earlier than the same clock time in the Central Time Zone.

Converting a time from one time zone to another

The second, very practical, thing the feature does is to change the time zone of a value that already has a time zone offset. So consider the time:

DECLARE @datetimeoffsetValue datetimeoffset(7) =
                '2017-09-01 20:41:28.9633676 -05:00'

This is one of the values used earlier, and this offset corresponds to the offset in the Central Time Zone.  To convert this to the Eastern Time Zone, you can use AT TIME ZONE again:

SELECT @datetimeoffsetValue AT TIME ZONE 'Eastern Standard Time'

----------------------------------
2017-09-01 21:41:28.9633676 -04:00

Which is one clock hour later in the Eastern Time Zone (and the corresponding offset is one hour less too). Casting the values to datetime2 value, it will strip off the time zone offset, and then it would look like 1 hour different.

As I was doing some research trying to find examples of AT TIME ZONE, I found the following thread on Stack Overflow: (https://stackoverflow.com/questions/36393742/using-at-time-zone-to-get-current-time-in-specified-time-zone )

Since the value in the Eastern Time Zone is a DATETIMEOFFSET value, you can simply use another AT TIME ZONE clause to convert the time zone to a different time zone. For example, at 10:00AM in the Eastern Time Zone, what time is it in the Pacific? (note that you cannot use AT TIME ZONE on a string literal… )

SELECT CAST('2017-01-01 10:00' AS datetime2) AT TIME ZONE 'Eastern Standard Time' AT TIME ZONE 'Pacific Standard Time'

It is 3 hours earlier:

----------------------------------
2017-01-01 07:00:00.0000000 -08:00

What makes this a very practical feature that many programmers need is to translate a time from their local time zone to the UTC time zone. For example:

DECLARE @TimeInUTC datetime2 = '20170101 12:00'
SELECT @TimeInUTC, @TimeInUTC AT TIME ZONE 'Eastern Standard Time' AT TIME ZONE 'UTC'

This returns:

--------------------------- ----------------------------------
2017-01-01 12:00:00.0000000 2017-01-01 17:00:00.0000000 +00:00

Because the value can be an expression, consider that you could build the following table, storing the time zone by name (Or perhaps calculating it by location the time zone the data based on the geography of the customer):

CREATE TABLE dbo.CustomerActivity
(
    CustomerId    int CONSTRAINT PKCustomerActivity PRIMARY KEY,
    ActivityUtcTime datetime2,
    TimeZoneName sysname
);
GO
INSERT INTO dbo.CustomerActivity(CustomerId, ActivityUtcTime,TimeZoneName)
VALUES(1,SYSUTCDATETIME(),'Eastern Standard Time'),(2,SYSUTCDATETIME(),'Pacific Standard Time'),
      (3,SYSUTCDATETIME(),'UTC'),(4,SYSUTCDATETIME(),'AUS Eastern Standard Time'),
      (5,SYSUTCDATETIME(),'Not A Time Zone'); --To force a failure

Query the data, casting the data first to DATETIMEOFFSET (which is equivalent in this case to AT TIME ZONE 'UTC'):

SELECT CustomerActivity.CustomerId,
       CAST(CAST(ActivityUtcTime AS datetimeoffset(7)) AT TIME ZONE TimeZoneName AS datetime2(7)) AS ActivityTimeLocal,
       CustomerActivity.TimeZoneName
FROM   dbo.CustomerActivity;

Then you can get back the times in the local time for the customer when you need it:

CustomerId  ActivityTimeLocal           TimeZoneName
----------- --------------------------- ------------------------------
1           2017-09-01 22:12:07.1144281 Eastern Standard Time
2           2017-09-01 19:12:07.1144281 Pacific Standard Time
3           2017-09-02 02:12:07.1144281 UTC
4           2017-09-02 12:12:07.1144281 AUS Eastern Standard Time

This lets you store the times as UTC, which is the typical desired way to store data when dealing with localities, particularly events where the overlapping time during the "Fall Back" part of Daylight Saving Time would be inconvenient. Alternatively, you might store data in your local time zone and convert not from UTC, but from the literal local value, but to the variable based customer's time zone.

Note that my row #5 will fail because of an invalid time zone:

Msg 9820, Level 16, State 1, Line 52
The time zone parameter 'Not A Time Zone' provided to AT TIME ZONE clause is invalid.

You can see the valid ones IN the following table, which will return 135 rows...

SELECT * FROM sys.time_zone_info

One thing that is really interesting is that most of the time zones are suffixed "Standard Time", though it is the same during Daylight Saving Time, which is generally part of the name, as in "Eastern Daylight Saving Time", rather than just "Eastern Time."

Published Friday, September 1, 2017 7:48 PM by drsql
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Links to my other sites

Archives

Privacy Statement