Mastering Time Zone Conversions in SQL Server with AT TIME ZONE

Mastering Time Zone Conversions in SQL Server with AT TIME ZONE

Most large database systems now will include data that describes events or entities in different time zones.  Various mechanisms can be used to handle data from different time zones.  For example, all dates could be converted to a single time zone, or they can be stored using DATETIMEOFFSET.  Each has benefits and drawbacks. 

We recently had a situation where the data source was providing date times in local time and had been for several years.  This data had only ever been expected to be analysed in regard to local time, so UTC times were irrelevant.  Recently that data source added a new data point which was stored as UTC, inconsistent with other dates in the source.  We needed to convert the local times to UTC, or the UTC to local time to be able to measure the difference between them. 

Researching the best way to do that led me to discover a feature I hadn’t noticed before in SQL, AT TIME ZONE.

What is AT TIME ZONE?

Introduced in SQL Server 2016, the AT TIME ZONE function allows you to convert datetime values between different time zones. It handles daylight saving time (DST) adjustments automatically, making it a powerful tool for developers and database administrators.

How Does It Work?

The AT TIME ZONE function converts a datetime value to a specified time zone. You can also chain it to convert the time to another timezone, such as UTC. Here's the basic syntax:

datetime_expression AT TIME ZONE time_zone

• datetime_expression: The datetime, datetime2, or datetimeoffset value you want to convert.
• time_zone: The target time zone.

Practical Examples

1. Convert to a Specific Time Zone

Suppose you have a datetime value and want to convert it to Pacific Standard Time (PST):

SELECT GETDATE() AT TIME ZONE 'Pacific Standard Time';        

This converts the current date and time to PST, considering any DST adjustments. Bear in mind that not all timezones use daylight saving, and those that do, don't all change on the same day and time. This feature is smart enough to handle that accurately.

1. Convert to UTC

To convert a datetime value to UTC, you can chain AT TIME ZONE:

SELECT GETDATE() AT TIME ZONE 'Pacific Standard Time' AT TIME ZONE 'UTC';        

This first converts the current date and time to PST and then to UTC.

This screenshot shows the results of each stage of this query.  Note the original time with no offset or time zone, using the function once adds an offset but leaves the time unchanged, chaining together converts the time to the second time zone and updates the offset. 

1. Handling Multiple Time Zones

If you have a table with datetime2 values and a column indicating the time zone, you can convert all times to UTC:

UPDATE DateTimeTable
SET UTCDateTime = DateTimeValue AT TIME ZONE TimeZone AT TIME ZONE 'UTC';

        

This ensures all datetime2 values are consistently converted to UTC, making it easier to handle and compare times across different regions.

Why Use AT TIME ZONE?

• Simplicity: It simplifies the complex task of time zone conversion.
• Accuracy: Automatically handles DST adjustments.
• Consistency: Ensures datetime values are consistent across different time zones.

Our Use Case

This function was valuable for my solution as the data stored the date the order was made, and the date and time that the order was dispatched.  Beyond our control was that the data source stored [OrderTime] as local time, and [DispatchTime] as UTC.  Having 600 local customer databases distributed across 5 continents meant that the time zone had to be corrected. 

Considerations

For this to work, you need to know the time zone that the datetime field was stored in, and need to use the correct spelling for the time zone, matching the list that SQL Server stores at [sys].[time_zone_info].

Conclusion

The AT TIME ZONE feature in SQL Server is a game-changer for handling time zone conversions. By leveraging this function, you can ensure your datetime values are accurate, consistent, and easy to manage, no matter where your users are located.