Rob Farley

AT TIME ZONE – a new favourite feature in SQL Server 2016

July 28, 2016 by in SQL Plan, T-SQL Queries | 34 Comments
Free eBook : Query Optimization with SentryOne Plan Explorer
SentryOne eBooks

In these books, you will find useful, hand-picked articles that will help give insight into some of your most vexing performance problems. These articles were written by several of the SQL Server industry’s leading experts, including Paul White, Paul Randal, Jonathan Kehayias, Erin Stellato, Glenn Berry, Aaron Bertrand, and Joe Sack.

Register to Download

Featured Author

Erin Stellato is a Principal Consultant with SQLskills and a Microsoft Data Platform MVP.

Erin’s Posts


Image © Mark Boyle | Australia Day Council of NSW.
Images property of respective artist(s). All rights reserved.

This is such a cool feature and I hadn’t noticed it until recently, even though Microsoft have had a page about it since December.

I live in Adelaide in Australia. And like over a billion other people in the world, Adelaide people have to cope with being on a half-hour time zone. In winter time, we’re UTC+9:30, and in summer time we’re UTC+10:30. Except that if you’re reading this in the northern hemisphere, you’ll need to remember that by ‘winter’, I mean April to October. Summer time is October to April, and Santa Claus sits on the beach with a cold drink, sweating through his thick red suit and beard. Unless he’s out saving lives, of course.

Within Australia, we have three main time zones (Western, Central, and Eastern), but this extends to five in the summer, as the three states which extend to the northern end of Australia (WA, Qld, and NT) don’t try to save any daylight. They’re close enough to the equator to not care, or something like that. It’s loads of fun for the Gold Coast airport, whose runway crosses the NSW-QLD border.

One of my favourite, but often unappreciated, features of SQL 2008 was the data type datetimeoffset. This allows date/time data to be stored with the time zone as well, such as '20160101 00:00 +10:30', which is when we celebrated New Year in Adelaide this year. To see when that was in US Eastern, I can use the function SWITCHOFFSET.

SELECT SWITCHOFFSET('20160101 00:00 +10:30', '-05:00');
 
-- 2015-12-31 08:30:00.0000000 -05:00

This is the same moment in time, but in a different part of the world. If I were on the phone to someone in North Carolina or New York, wishing them a Happy New Year because it was just past midnight in Adelaide, they would be saying “What do you mean? It’s still breakfast time here on New Year’s Eve!”

The problem is that to do this, I need to know that in January, Adelaide is +10:30 and US Eastern is –5:00. And that’s often a pain. Especially if I’m asking about late March, early April, October, early November – those times of year when people can’t be sure which time zone people in other countries were in because they change by an hour for daylight saving, and they all do so according to different rules. My computer tells me what time zone people are in now, but it’s much harder to tell what time zone they will be in at other times of the year.

Database servers often run in UTC, because it’s simply easier to not have to deal with time zones. Many years ago I remember having to fix a report which listed incidents that occurred along with response times. Measuring SLA was quite straight forward – I could see that one incident happened during the customer’s working hours, and that they responded within one hour. I could see that another incident occurred outside working hours, and the response was within two hours. The problem came when a report was produced at the end of a month when the time zone changed, causing an incident that actually happened at 5:30pm (outside hours) to be listed as if it had occurred at 4:30pm (inside hours). The response had taken about 90 minutes, which was okay, but the report was showing otherwise.

All this is fixed in SQL Server 2016.

Now, instead of saying: '20160101 00:00 +10:30', I can start with a datetime value which does not have a time zone offset, and use AT TIME ZONE to explain that it’s in Adelaide.

SELECT CONVERT(datetime,'20160101 00:00') 
    AT TIME ZONE 'Cen. Australia Standard Time';
 
-- 2016-01-01 00:00:00.000 +10:30

And this can be converted to the American time by appending AT TIME ZONE again.

SELECT CONVERT(datetime,'20160101 00:00') 
    AT TIME ZONE 'Cen. Australia Standard Time' 
    AT TIME ZONE 'US Eastern Standard Time';
 
-- 2015-12-31 08:30:00.000 -05:00

Now, I know this is a lot more longwinded. And I need to explicitly convert the string to datetime, to avoid an error saying:

Argument data type varchar is invalid for argument 1 of AT TIME ZONE function.

But despite the longwindedness of it, I love it, because at no point did I need to figure out that Adelaide was in +10:30, or that Eastern was -5:00 – I simply needed to know the time zone by name. Figuring out whether daylight saving should apply or not was handled for me.

It works by using the Windows registry, which has all that information in it, but sadly, it’s not perfect when looking back in time. Australia changed the dates in 2008, and the US changed its dates in 2005 – both countries saving daylight for more of the year. AT TIME ZONE understands this. But it doesn’t seem to appreciate that in Australia in the year 2000, thanks to the Sydney Olympics, Australia started daylight saving about two months earlier. This is a little frustrating, but it’s not SQL’s fault – we need to blame Windows for that. I guess the Windows registry doesn’t remember the hotfix that went around that year. (Note to self: I might need to ask someone in the Windows team to fix that…)

The usefulness just continues though!

That time zone name doesn’t even need to be a constant. I can pass variables in, and even use columns:

WITH PeopleAndTZs AS
(
  SELECT * FROM (VALUES 
    ('Rob',   'Cen. Australia Standard Time'),
    ('Paul',  'New Zealand Standard Time'),
    ('Aaron', 'US Eastern Standard Time')
  ) t (person, tz)
)
SELECT tz.person, SYSDATETIMEOFFSET() AT TIME ZONE tz.tz
  FROM PeopleAndTZs tz;
 
/*
  Rob      2016-07-18 18:29:11.9749952 +09:30
  Paul     2016-07-18 20:59:11.9749952 +12:00
  Aaron    2016-07-18 04:59:11.9749952 -04:00
*/

(Because I ran that just before 6:30pm here in Adelaide, which happens to be nearly 9pm in New Zealand where Paul is, and nearly 5am this morning in the eastern bit of America where Aaron is.)

This would let me easily see what time it is for people wherever they are in the world, and to see who would be best to respond to some issue. And even more so, it would let me do it for people in the past. I could have a report which analyses which time zones would allow the greatest number of events to occur during business hours.

Those time zones are listed in sys.time_zone_info, along with what the current offset is, and whether daylight saving is currently applied.

name

current_utc_offset

is_currently_dst
Singapore Standard Time

+08:00

0
W. Australia Standard Time

+08:00

0
Taipei Standard Time

+08:00

0
Ulaanbaatar Standard Time

+09:00

1
North Korea Standard Time

+08:30

0
Aus Central W. Standard Time

+08:45

0
Transbaikal Standard Time

+09:00

0
Tokyo Standard Time

+09:00

0

Sampling of rows from sys.time_zone_info

I’m only really interested in what the name is, but anyway. And it’s interesting to see that there is a time zone called “Aus Central W. Standard Time” which is on the quarter-hour. Go figure. Also worth noting that places are referred to using their Standard Time name, even if they’re currently observing DST. Such as Ulaanbaatar in that list above, which isn’t listed as Ulaanbaatar Daylight Time.

Now, I’m sure you’re wondering what the impact of this might be on indexing.

In terms of the shape of the plan, it’s no different to dealing with datetimeoffset in general. If I have datetime values, such as in the AdventureWorks column Sales.SalesOrderHeader.OrderDate (upon which I created an index called rf_IXOD), then running both this query:

select OrderDate, SalesOrderID
  from Sales.SalesOrderHeader
  where OrderDate >= convert(datetime,'20110601 00:00') at time zone 'US Eastern Standard Time' 
    and OrderDate <  convert(datetime,'20110701 00:00') at time zone 'US Eastern Standard Time' ;

And this query:

select OrderDate, SalesOrderID
  from Sales.SalesOrderHeader
  where OrderDate >= convert(datetimeoffset,'20110601 00:00 -04:00')
    and OrderDate <  convert(datetimeoffset,'20110701 00:00 -04:00');

In both cases, you get plans that look like this:

Identical plans for above queries

But exploring a little more closely, there is a problem.

The one that uses AT TIME ZONE doesn’t use the statistics very well. It thinks it’s going to see 5,170 rows come out of that Index Seek, when there’s actually only 217. Why 5,170? Well, Aaron’s recent post, “Paying Attention To Estimates,” explains it, by referring to the post “Cardinality Estimation for Multiple Predicates” from Paul. 5,170 is 31,465 (rows in the table) * 0.3 * sqrt(0.3).

Estimates

The second query gets it right, estimating 217. No functions involved, you see.

This is probably fair enough. I mean – at the point when it’s producing the plan, it won’t have asked the registry for the information it needs, so it really doesn’t know how many to estimate. But there is potential for it to be a problem.

If I add extra predicates which I know can’t be a problem, then my estimates actually drop even further – down to 89.9 rows.

select OrderDate, SalesOrderID
  from Sales.SalesOrderHeader
  where OrderDate >= convert(datetime,'20110601 00:00') at time zone 'US Eastern Standard Time' 
  and OrderDate   <  convert(datetime,'20110701 00:00') at time zone 'US Eastern Standard Time'
  and OrderDate   >= convert(datetimeoffset,'20110601 00:00 +14:00')
  and OrderDate   <  convert(datetimeoffset,'20110701 00:00 -12:00');

Estimating too many rows means too much memory is allocated, but estimating too few can cause too little memory, with potentially needing a spill to correct the problem (which can often be disastrous from a performance perspective). Go read Aaron’s post for more information about how poor estimates can be bad.

When I consider how to handle displaying values for those people from before, I can use queries like this:

WITH PeopleAndTZs AS
(
  SELECT * FROM (VALUES 
    ('Rob',   'Cen. Australia Standard Time'),
    ('Paul',  'New Zealand Standard Time'),
    ('Aaron', 'US Eastern Standard Time')
  ) t (person, tz)
)
SELECT tz.person, o.SalesOrderID, o.OrderDate AT TIME ZONE 'UTC' AT TIME ZONE tz.tz
FROM PeopleAndTZs tz
CROSS JOIN Sales.SalesOrderHeader o
WHERE o.SalesOrderID BETWEEN 44001 AND 44010;

And get this plan:

image

…which has no such concerns – the right-most Compute Scalar is converting the datetime OrderDate into datetimeoffset for UTC, and the left-most Compute Scalar is converting it into the appropriate time zone for the person. The warning is because I’m doing a CROSS JOIN, and that was fully intentional.

AT TIME ZONE isn’t perfect. But it is really useful – incredibly so. It’s flexible enough to use columns and variables, and I can see a huge amount of potential for it. But if it’s going to cause my estimates to be out, then I’m going to need to be careful. For display purposes, this shouldn’t matter at all though, and that’s where I can see it being most useful.

This really is one of my favourite features of SQL Server 2016. I’ve been crying out for something like this for a very long time.

Oh, and most of those billion people on the half-hour time zone are in India. But you probably already knew that…