AT TIME ZONE: The easy way to deal with time zones and daylight savings time

Published on: 2018-03-27

Many people think daylight savings time was created to help align the hours that the sun is up with our waking hours so farmers and school children didn’t have to be outside in the dark.  Or something like that.

Well let me fill you in on a little secret – daylight savings time was actually created by a government works project to ensure that programmers could forever write tedious conditional logic in their date-based queries to handle the date time conversions surrounding time zones and daylight savings time.

While these types of datetime conversions have historically been a pain, SQL Server 2016 introduced the AT TIME ZONE feature makes these types of conversions a breeze.

Defining Time Zone

Let’s say you have a datetime value that you know is encoded in UTC (if you don’t know what timezone your data was originally encoded in you’re out of luck):

Besides naming convention, there’s nothing that tells us that our datetime is in UTC.  But if we know that to be the case, we can use AT TIME ZONE to add an UTC offset to the timestamp to make it official:

See that +00:00  at the end of our value?  That’s our time zone offset – it’s basically telling us how many hours and minutes away from UTC our date is stored in.  With this offset in place, our UTC datetime isn’t encoded only by the variable name – it’s actually encoded in the data itself.

Converting Time Zones

Now that we have a well-defined UTC datetime, we can use AT TIME ZONE to easily convert our data to different time zones.

For example, if we want to convert our UTC datetime to Eastern Standard Time, we can tack on  AT TIME ZONE ‘Eastern Standard Time’ :

The time portion of our datetime is now showing 7am with the offset indicating we are 4 hours behind UTC.  Easy time zone conversions, yes!

But What About Our Farmer Friends?

So AT TIME ZONE makes it easy to convert between time zones – but how does it handle daylight savings time conversions?

Well, on March 11, 2018 I lost an hour of my life when daylight savings time kicked in at 2am.  Does AT TIME ZONE recognize this theft?

YES! Say so long to conditional conditions that subtract an amount of time based on the date; AT TIME ZONE handles the switch to DST with ease as noted by the offset change from -05:00  to -04:00 .

And just like that, programming for time zone and day light savings time logic just became a little easier.

But I Don’t Live In The Eastern Standard Time Zone!

No problem, in addition to AT TIME ZONE, SQL Server 2016 added this nifty table that will give you the names of all of the time zones it supports:

select * from sys.time_zone_info

Thanks for reading. You might also enjoy following me on Twitter.

Want to learn even more SQL?

Sign up for my newsletter to receive weekly SQL tips!

10 thoughts on “AT TIME ZONE: The easy way to deal with time zones and daylight savings time”

    1. You’ve got eagle eyes Parker! You’re correct – I need a technical editor. I updated the post with the correction, thanks!

    1. Hi javier,
      The sys.time_zone_info view (https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-time-zone-info-transact-sql?view=sql-server-2017) pulls the timezone data from KEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Time Zones in the registry.

      I would hope that if a country abandons DST, that Microsoft would update the Windows Time Zones via a Windows Update and hopefully SQL Server would pull in the updated values…but I have not tested!

  1. after some further testing based on the example you presented I do not believe this SQL Server feature is accurate. Below is a revised example closer to the DST time switch….am I missing something?

    — UTC just before eastern timezone DST change (i.e. 1:58am EST)
    DECLARE @PreDST datetime = ‘2018-03-11 06:58’

    /* UTC just after eastern timezone DST time change
    (i.e. 2:01am ET which is actually 3:01 EDT
    since between 2am-3am does not exist – THE LOST HOUR) */
    DECLARE @PostDST datetime = ‘2018-03-11 07:01’

    SELECT @PreDST AT TIME ZONE ‘Eastern Standard Time’ [PreDST (should be -05:00)]
    SELECT @PostDST AT TIME ZONE ‘Eastern Standard Time’ as [PostDST (should be -04:00)]

    PreDST (should be -05:00)
    ———————————-
    2018-03-11 06:58:00.000 -04:00

    PostDST (should be -04:00)
    ———————————-
    2018-03-11 07:01:00.000 -04:00

    1. Hi Jeff,
      Great that you are thinking about these things as DST is only a few weeks away.

      The problem in your example is that the original @PreDST time is not in UTC like your comment suggests. If you check the documentation for AT TIME ZONE(https://docs.microsoft.com/en-us/sql/t-sql/queries/at-time-zone-transact-sql?view=sql-server-2017), it says:

      “When inputdate is provided without offset information, the function applies the offset of the time zone assuming that inputdate is in the target time zone.”

      That means to show what’s happening with your example, you either need to assume the @PresDST and @PostDST variables are in EST:

      DECLARE @PreDST datetime = ‘2018-03-11 01:58’
      DECLARE @PostDST datetime = ‘2018-03-11 02:01’

      SELECT @PreDST AT TIME ZONE ‘Eastern Standard Time’ [PreDST (should be -05:00)]
      SELECT @PostDST AT TIME ZONE ‘Eastern Standard Time’ as [PostDST (should be -04:00)]

      Or you need to explicitly state that those variables are in UTC:

      — No offset information included so SQL Server doesn’t know the time zone
      DECLARE @PreDST datetime = ‘2018-03-11 06:58’
      DECLARE @PostDST datetime = ‘2018-03-11 07:01’

      — Manually specify the source is UTC:
      SELECT @PreDST AT TIME ZONE ‘UTC’ AT TIME ZONE ‘Eastern Standard Time’ [PreDST (should be -05:00)]
      SELECT @PostDST AT TIME ZONE ‘UTC’ AT TIME ZONE ‘Eastern Standard Time’ as [PostDST (should be -04:00)]
      –or DATETIMEOFFSET defaults to UTC
      SELECT CONVERT(datetimeoffset , @PreDST) –AT TIME ZONE ‘Eastern Standard Time’ [PreDST (should be -05:00)]
      SELECT CONVERT(datetimeoffset , @PostDST) –AT TIME ZONE ‘Eastern Standard Time’ [PostDST (should be -04:00)]
      –or specifically keep your time in UTC with format 126
      SELECT CONVERT(datetime2(0), ‘2018-03-11T01:58:00’, 126) AT TIME ZONE ‘Eastern Standard Time’;
      SELECT CONVERT(datetime2(0), ‘2018-03-11T02:01:00’, 126) AT TIME ZONE ‘Eastern Standard Time’;

      This was a great question – maybe I will expand this into a post/video with the time change happening in the US in a few weeks 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.