Watch this week's video on YouTube
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
