SQL Server 2016: What Time Is It?

Last week Microsoft had their #datadriven event to kick off the launch of SQL Server 2016. If you haven’t heard about the announcements such as “data is the new electricity” and SQL Server on Linux then I can only imagine you are either (1) living in a shack in Montana or (2) not a Microsoft-centric person such as myself.

One item that was talked about as part of the launch event was how SQL Server has been used to map the Universe. This was, and is, one of the coolest things I have seen in a long time. Then again, I’m a bit of a space nerd.

The fact that my astronomy background is now intersecting with my data career and SQL Server is making me as happy as a Korean schoolgirl learning about emoticons for the first time

?(*´?`*)?

When I heard that SQL Server was being used to store information on the Universe I started thinking about a post from Karen López (blog | @datachick) regarding a new datatype in SQL Server 2014: spacetime. (It’s an April Fool’s day post, folks).

Well, this past weekend the United States entered into Daylight Saving Time (DST). (Yes, I said ‘Saving’, because it’s time, not a bank). For most of us in the data industry, the concept of DST is something we can’t stand. We would prefer to have everything stored in UTC time, to remove any ambiguity about the data that has been stored.

A Brief History of SQL Server Time

The launch of SQL Server 2005 gave us the ability to find the UTC date with the following function:

SELECT GETUTCDATE()

You would need to do some math between that result and the GETDATE() function in order to find your timezone offset, if you wanted. The purpose of such an exercise would be to display the time a GUI somewhere. The offset itself isn’t as important as the displaying of the local time to the application user.

Unless, of course, your time zone changes a couple of times a year. Or if different countries switch at different times. Or never at all. Such problems with knowing what “time” means to an end user lead to database designs that track two pieces of data: UTC and the local time of the server.

I don’t like storing the same piece of information twice. Neither should you.

SQL 2008 Solved Everything, Kinda

With SQL 2008 we got a new time function:

SELECT SYSDATETIMEOFFSET()

This was a nice step forward as it allowed for us to find the current time along with the current offset for the local machine in one easy step. Well, more than one step if we still wanted to know the exact UTC time. And you would either need to parse out the offset and store that, or just store the entire datetime offset itself and do the math later.

Oh, and did I mention that SYSDATETIMEOFFSET() is not DST aware. Well, it’s not.

So, in the end we would still be storing two dates (or more) because nobody ever can agree upon anything when it comes to date and time and GUIs and data storage.

SQL Server 2016: What Time Is It?

Today I found that SQL 2016 introduces a function to help alleviate the issues we have when it comes to finding the current time. No, it’s not spacetime (I wish), nor is it a stardate (how cool would that be?), it is the ability to transform a given time into your local time by using the AT TIME ZONE function.

[ASIDE: This is a feature that has existed in Azure SQL Database and is a GREAT example of how features from Azure are making their way into the boxed product. More on that in a future post, I promise.]

The syntax is simple enough:

SELECT CONVERT(datetime, '03/14/2016 01:01:00')
 AT TIME ZONE 'Eastern Standard Time'

Of course you will need to know what is allowed for you to use for the time zone name. Fortunately for us, this list is stored in the registry of the server. In other words, you can use whatever timezones are installed on the server. For a complete list you can query the sys.time_zone_info DMV:

SELECT *
 FROM sys.time_zone_info

Running that statement returns 109 rows on my SQL 2016 CTP3 instance, including such time zone classic names we all know and love such as “UTC -11” and “Ulaanbaatar Standard Time”, which I think might be near Jakku.

The real beauty of the sys.time_zone_info DMV is that it has columns for the offset as well as a column named is_currently_dst to indicate if that region is currently following the idea a bunch of Canadians in 1908 came up with after a hard night of drinking maple-flavored whiskey and wanted needed an extra hour of sleep. OK, that last part is a guess, but probably closer to the truth than anyone wants to admit.

The sys.time_zone_info DMV now allows for us to avoid building out our own calendar tables in order to store dates and times for our end users. A simple CTE can be used to replace what used to be a lot more code:

;WITH time_CTE (tz_name, is_currently_dst)
 AS
 (SELECT name, is_currently_dst
 FROM sys.time_zone_info)
 � 
 SELECT SYSUTCDATETIME()
� AT TIME ZONE tz_name as [Time]
� , tz_name
� , is_currently_dst
 FROM time_CTE

All in all, I’d rather we work on getting stardates into SQL Server vNext. But this new function seems useful, too.