This post is a follow up to my previous post on the performance impacts of the AT TIME ZONE implementation in SQL Server based on some observations and comments that were made on Twitter by my good friend Simon Sabin and then later in comments. The first observation was that the solution provided doesn’t actually handle the conversion correctly, since it is using the current information in sys.time_zone_info. To be honest, this was a huge oversight in writing the post; I originally considered this and had intended to point this important fact out in the post and didn’t. I am really grateful that it was brought up! The second observation by Simon was that I didn’t need to use AT TIME ZONE for the purpose I was originally using it, I could have used TODATETIMEOFFSET() to get the result I wanted and it would have avoided the performance issues discovered with AT TIME ZONE. Live and learn….
However, not wanting to just leave an incorrect solution on my blog, or just say “Hey this doesn’t actually do what it is supposed to as a solution!”, I actually started working on how to solve this so that proper daylight savings time information could be applied similar to AT TIME ZONE without the performance overhead, regardless of what the date range or time zone requirement was. I hope that this post explains how to do it.
Getting Time Zone DST Adjustment Rules
The basis for the approach in this post is going to be the TimeZoneInfo class from .NET, which is “supposed” to be up to date with any time zone DST rules from the Windows Registry. I used a modification of the PowerShell example in the .NET Docs page for the TimeZoneInfo class to build a a flat file that is then bulk loaded to a table in SQL Server using BCP with all the time zone adjustment rules:
# Get timezone/date details and open a stream writer
$DateFormats = [System.Globalization.CultureInfo]::CurrentCulture.DateTimeFormat
$TimeZones = [System.TimeZoneInfo]::GetSystemTimeZones()
$OutputFileName = 'C:\TimeZoneInfo.txt'
$Sw = New-Object -TypeName System.IO.StreamWriter -ArgumentList $OutputFileName,$false
# Write overview information
#$Sw.WriteLine('{0} Time zones on this system' -f $TimeZones.Count)
$sw.WriteLine("ID|DisplayName|StandardName|HasDST|UTCOffsetHours|UTCOffsetMinutes|RuleNumber|RuleStartDate|RuleEndDate|IsFixedDateRule|DeltaMinutes|BeginsMonthOfYear|BeginsWeekOfMonth|BeginsDayOfWeek|BeginsTimeOfDay|EndsMonthOfYear|EndsWeekOfMonth|EndsDayOfWeek|EndsTimeOfDay");
# Process each timezone on the system
# Write details to the streamwriter
foreach ($TimeZone in $TimeZones)
{
$HasDST = $TimeZone.SupportsDaylightSavingTime
$OffsetFromUtc = $TimeZone.BaseUtcOffset
$AdjustRules = $timeZone.GetAdjustmentRules()
$RuleNum = 0;
if ($AdjustRules.Count -gt 0)
{
foreach ($Rule in $AdjustRules)
{
$RuleNum++;
$TransTimeStart = $Rule.DaylightTransitionStart
$TransTimeEnd = $Rule.DaylightTransitionEnd
$Sw.Write("{0}|" -f $TimeZone.Id)
$Sw.Write("{0}|" -f $TimeZone.DisplayName)
$Sw.Write("{0}|" -f $TimeZone.StandardName)
$Sw.Write("{0}|" -f $(If ($HasDST) {"1"} Else {"0"}))
$Sw.Write("{0}|" -f $OffsetFromUtc.Hours)
$Sw.Write("{0}|" -f $OffsetFromUtc.Minutes)
$Sw.Write("{0}|" -f $RuleNum)
$Sw.Write("{0}|" -f $(If ($Rule.DateStart -eq [DateTime]::MinValue) {[System.Data.SqlTypes.SqlDateTime]::MinValue} else {$Rule.DateStart}))
$Sw.Write("{0}|" -f $(If ($Rule.DateEnd -eq [DateTime]::MaxValue) {[System.Data.SqlTypes.SqlDateTime]::MaxValue} else {$Rule.DateEnd}))
$Sw.Write("{0}|" -f $(If ($TransTimeStart.IsFixedDateRule){"1"} Else {"0"}))
$Sw.Write("{0}|" -f $Rule.DaylightDelta.TotalMinutes)
$Sw.Write("{0}|" -f $TransTimeStart.Month)
$Sw.Write("{0}|" -f $TransTimeStart.Week)
$Sw.Write("{0}|" -f $TransTimeStart.Day)
$Sw.Write("{0}|" -f $TransTimeStart.TimeOfDay.ToString("T"))
$Sw.Write("{0}|" -f $TransTimeEnd.Month)
$Sw.Write("{0}|" -f $TransTimeEnd.Week)
$Sw.Write("{0}|" -f $TransTimeEnd.Day)
$Sw.WriteLine("{0}" -f $TransTimeEnd.TimeOfDay.ToString("T"))
}
}
else
{
$RuleNum++;
$TransTimeStart = $Rule.DaylightTransitionStart
$TransTimeEnd = $Rule.DaylightTransitionEnd
$Sw.Write("{0}|" -f $TimeZone.Id)
$Sw.Write("{0}|" -f $TimeZone.DisplayName)
$Sw.Write("{0}|" -f $TimeZone.StandardName)
$Sw.Write("{0}|" -f $(If ($HasDST) {"1"} Else {"0"}))
$Sw.Write("{0}|" -f $OffsetFromUtc.Hours)
$Sw.Write("{0}|" -f $OffsetFromUtc.Minutes)
$Sw.Write("{0}|" -f [string]::Empty)
$Sw.Write("{0}|" -f [string]::Empty)
$Sw.Write("{0}|" -f [string]::Empty)
$Sw.Write("{0}|" -f [string]::Empty)
$Sw.Write("{0}|" -f [string]::Empty)
$Sw.Write("{0}|" -f [string]::Empty)
$Sw.Write("{0}|" -f [string]::Empty)
$Sw.Write("{0}|" -f [string]::Empty)
$Sw.Write("{0}|" -f [string]::Empty)
$Sw.Write("{0}|" -f [string]::Empty)
$Sw.Write("{0}|" -f [string]::Empty)
$Sw.Write("{0}|" -f [string]::Empty)
$Sw.WriteLine("{0}" -f [string]::Empty)
}
}
# Close stream writer then display output in notepad
$Sw.Close()
$TableDefinition = "DROP TABLE IF EXISTS [dbo].[TimeZoneInfoNew];
CREATE TABLE [dbo].[TimeZoneInfoNew](
[ID] [varchar](31) NULL,
[DisplayName] [varchar](61) NULL,
[StandardName] [varchar](31) NULL,
[HasDST] [smallint] NULL,
[UTCOffsetHours] [smallint] NULL,
[UTCOffsetMinutes] [smallint] NULL,
[RuleNumber] [smallint] NULL,
[RuleStartDate] [datetime] NULL,
[RuleEndDate] [datetime] NULL,
[IsFixedDateRule] [smallint] NULL,
[DeltaMinutes] [smallint] NULL,
[BeginsMonthOfYear] [smallint] NULL,
[BeginsWeekOfMonth] [smallint] NULL,
[BeginsDayOfWeek] [smallint] NULL,
[BeginsTimeOfDay] [datetime] NULL,
[EndsMonthOfYear] [smallint] NULL,
[EndsWeekOfMonth] [smallint] NULL,
[EndsDayOfWeek] [smallint] NULL,
[EndsTimeOfDay] [datetime] NULL
)"
Invoke-Sqlcmd -ServerInstance . -Database AdventureWorks2014 -Query $TableDefinition
bcp AdventureWorks2014.dbo.TimeZoneInfoNew format nul -f C:\timezoneinfo.fmt -c -x -t"|" -T -r"\n"
$InsertCommand = "INSERT INTO [dbo].[TimeZoneInfoNew]
SELECT *
FROM OPENROWSET(BULK 'C:\TimeZoneInfo.txt', FORMATFILE='C:\TimeZoneInfo.fmt', FIRSTROW=2) AS a
";
Invoke-Sqlcmd -ServerInstance . -Database AdventureWorks2014 -Query $InsertCommand
Transforming the Rules Into Something Useable
This is where I started to have problems! The adjustment rules aren’t stored as DST starts on X datetime and ends on Y datetime. The rules have a start date and ending date which really only tells you what years those rules are applied to. Then there are three pieces of data that you have to find the actual date of each year within the start and end date ranges for when DST starts and ends; the month of the year (1-12 as normal), the week of the month (this one gets really tricky), the day of the week (1=Sunday-7=Saturday). These are all common columns of a date dimension, so I went to one of my goto resources for utilities like a date dimension and grabbed the code from Aaron Bertrand’s article Creating a date dimension or calendar table in SQL Server.
After building the dbo.DateDimension table I started trying to define the JOIN’s on the three elements that identify the date in each year and this is where I ran into problems. The month of the year was fine, but the week of the month was challenging. It matched for some months for some years but not others, and what is actually Week 1 of a month isn’t actually Week 1 of the month, but the first week for that day in that month. Thankfully the dbo.DateDimension table from Aaron actually already has this worked out in it as two separate columns TheWeekOfMonth (which is what expected to be using), and then TheDayOfWeekInMonth which is the ordinal week for that specific day of week in the monthly occurrences. However, you can’t just pick one or the other it turns out, you have to use both of them under different conditions which was where I got stuck for a while. Sometimes the best thing is to step away and come back to things. Here is what I ultimately came up with for DST rules in SQL usable format:
SELECT
DST.[ID],
DST.[DisplayName],
DST.[StandardName],
DST.[HasDST],
DST.[UTCOffsetHours],
DST.[UTCOffsetMinutes],
ISNULL(DST.[DeltaMinutes],0) AS DeltaMinutes,
ISNULL(DATEADD(ms, DATEDIFF(ms, 0, CAST(DST.BeginsTimeOfDay AS TIME)), CAST(startdim.TheDate AS DATETIME)), '1753-01-01 00:00:00.000') AS StartDateTime,
ISNULL(DATEADD(ms, DATEDIFF(ms, 0, CAST(DST.EndsTimeOfDay AS TIME)), CAST(enddim.TheDate AS DATETIME)), '9999-12-31 00:00:00.000') AS EndDateTime
FROM [dbo].[TimeZoneInfoNew] AS DST
LEFT OUTER JOIN dbo.DateDimension AS startdim
ON (startdim.TheDate >= DST.RuleStartDate AND
CASE
WHEN EXISTS (SELECT 1
FROM dbo.DateDimension AS dd
WHERE dd.TheDate >= DST.RuleStartDate AND
dd.TheDayOfWeekInMonth = DST.BeginsWeekOfMonth AND
dd.TheMonth = DST.BeginsMonthOfYear AND
dd.TheDayOfWeek = DST.BeginsDayOfWeek AND
dd.TheDate <=DST.RuleEndDate)
THEN startDim.TheDayOfWeekInMonth
ELSE startDim.TheWeekOfMonth
END = DST.BeginsWeekOfMonth AND
startdim.TheMonth = DST.BeginsMonthOfYear AND
startdim.TheDayOfWeek = DST.BeginsDayOfWeek AND
startdim.TheDate <=DST.RuleEndDate)
LEFT OUTER JOIN dbo.DateDimension AS enddim
ON (enddim.TheDate >= startdim.TheDate AND
CASE
WHEN EXISTS (SELECT 1
FROM dbo.DateDimension AS dd
WHERE dd.TheDate >= startdim.TheDate AND
dd.TheDayOfWeekInMonth = DST.EndsWeekofMonth AND
dd.TheMonth = DST.EndsMonthOfYear AND
dd.TheDayOfWeek = DST.EndsDayOfWeek AND
dd.TheYear = CASE
WHEN (DST.BeginsMonthOfYear > DST.EndsMonthOfYear)
THEN startdim.TheYear+1
ELSE startdim.TheYear
END AND
dd.TheDate <= CASE
WHEN DST.BeginsMonthOfYear > DST.EndsMonthOfYear AND DST.RuleEndDate < '9999/12/01'
THEN DATEADD(YYYY,1,DST.RuleEndDate)
ELSE DST.RuleEndDate
END)
THEN enddim.TheDayOfWeekInMonth
ELSE enddim.TheWeekOfMonth
END = DST.EndsWeekOfMonth AND
enddim.TheMonth = DST.EndsMonthOfYear AND
enddim.TheDayOfWeek = DST.EndsDayOfWeek AND
enddim.TheYear = CASE
WHEN (DST.BeginsMonthOfYear > DST.EndsMonthOfYear)
THEN startdim.TheYear+1
ELSE startdim.TheYear END AND
enddim.TheDate <= CASE WHEN DST.BeginsMonthOfYear > DST.EndsMonthOfYear AND DST.RuleEndDate < '9999/12/01' THEN DATEADD(YYYY,1,DST.RuleEndDate) ELSE DST.RuleEndDate END
)
Testing the Rules
Testing the rules was probably one of the biggest delays to getting this blog post written, and I wouldn’t be surprised for someone to something I missed as an exception from this post. Before you use anything make sure that you validate it is actually accurate. To my best ability in quality checking the 2.4million calculated dates across all time zones, I believe I have accurately calculated things, but there could be something hiding in here still. I started out by creating a inline TVF that would behave as a duplicate for the sys.time_zone_info DMV, which would mean they should output the same results for any given date if we played around with the system time changing the dates.
DROP FUNCTION IF EXISTS dbo.tvf_time_zone_info;
GO
CREATE FUNCTION dbo.tvf_time_zone_info
(
@thedate DATETIME = NULL
)
RETURNS TABLE
AS RETURN (
WITH Rules AS (
SELECT
DST.[ID],
DST.[DisplayName],
DST.[StandardName],
DST.[HasDST],
DST.[UTCOffsetHours],
DST.[UTCOffsetMinutes],
ISNULL(DST.[DeltaMinutes],0) AS DeltaMinutes,
ISNULL(DATEADD(ms, DATEDIFF(ms, 0, CAST(DST.BeginsTimeOfDay AS TIME)), CAST(startdim.TheDate AS DATETIME)), '1753-01-01 00:00:00.000') AS StartDateTime,
ISNULL(DATEADD(ms, DATEDIFF(ms, 0, CAST(DST.EndsTimeOfDay AS TIME)), CAST(enddim.TheDate AS DATETIME)), '9999-12-31 00:00:00.000') AS EndDateTime
FROM [dbo].[TimeZoneInfoNew] AS DST
LEFT OUTER JOIN dbo.DateDimension AS startdim
ON (startdim.TheDate >= DST.RuleStartDate AND
CASE
WHEN EXISTS (SELECT 1
FROM dbo.DateDimension AS dd
WHERE dd.TheDate >= DST.RuleStartDate AND
dd.TheDayOfWeekInMonth = DST.BeginsWeekOfMonth AND
dd.TheMonth = DST.BeginsMonthOfYear AND
dd.TheDayOfWeek = DST.BeginsDayOfWeek AND
dd.TheDate <=DST.RuleEndDate)
THEN startDim.TheDayOfWeekInMonth
ELSE startDim.TheWeekOfMonth
END = DST.BeginsWeekOfMonth AND
startdim.TheMonth = DST.BeginsMonthOfYear AND
startdim.TheDayOfWeek = DST.BeginsDayOfWeek AND
startdim.TheDate <=DST.RuleEndDate)
LEFT OUTER JOIN dbo.DateDimension AS enddim
ON (enddim.TheDate >= startdim.TheDate AND
CASE
WHEN EXISTS (SELECT 1
FROM dbo.DateDimension AS dd
WHERE dd.TheDate >= startdim.TheDate AND
dd.TheDayOfWeekInMonth = DST.EndsWeekofMonth AND
dd.TheMonth = DST.EndsMonthOfYear AND
dd.TheDayOfWeek = DST.EndsDayOfWeek AND
dd.TheYear = CASE
WHEN (DST.BeginsMonthOfYear > DST.EndsMonthOfYear)
THEN startdim.TheYear+1
ELSE startdim.TheYear
END AND
dd.TheDate <= CASE
WHEN DST.BeginsMonthOfYear > DST.EndsMonthOfYear AND DST.RuleEndDate < '9999/12/01'
THEN DATEADD(YYYY,1,DST.RuleEndDate)
ELSE DST.RuleEndDate
END)
THEN enddim.TheDayOfWeekInMonth
ELSE enddim.TheWeekOfMonth
END = DST.EndsWeekOfMonth AND
enddim.TheMonth = DST.EndsMonthOfYear AND
enddim.TheDayOfWeek = DST.EndsDayOfWeek AND
enddim.TheYear = CASE
WHEN (DST.BeginsMonthOfYear > DST.EndsMonthOfYear)
THEN startdim.TheYear+1
ELSE startdim.TheYear END AND
enddim.TheDate <= CASE WHEN DST.BeginsMonthOfYear > DST.EndsMonthOfYear AND DST.RuleEndDate < '9999/12/01' THEN DATEADD(YYYY,1,DST.RuleEndDate) ELSE DST.RuleEndDate END
)
),
AllDates AS (
SELECT *
FROM [dbo].[DateDimension] AS d
CROSS JOIN (SELECT DISTINCT [ID], [DisplayName], [StandardName], [UTCOffsetHours], [UTCOffsetMinutes] FROM Rules) AS n
)
SELECT DISTINCT d.[ID] AS [name],
CASE WHEN COALESCE(r.[UTCOffsetHours], d.[UTCOffsetHours]) > 0 THEN '+' ELSE '-' END + LEFT(
CONVERT(VARCHAR(5),
DATEADD(
mi, -1*ISNULL(r.[DeltaMinutes],0),
DATEADD(mi, ABS(COALESCE(r.[UTCOffsetMinutes], d.[UTCOffsetMinutes])),
DATEADD(hh, ABS(COALESCE(r.[UTCOffsetHours], d.[UTCOffsetHours])), '1900/01/01')
)), 108), 5) AS [current_utc_offset],
CASE WHEN r.DeltaMinutes > 0 THEN 1 ELSE 0 END AS [is_currently_dst]
FROM AllDates AS d
LEFT OUTER JOIN Rules AS r ON d.StandardName = r.StandardName
AND d.TheDate >= r.StartDatetime AND d.TheDate < r.EndDateTime
WHERE ISNULL(@thedate, CAST(GETDATE() AS date)) = d.TheDate
)
GO
After finding more bugs and differences than I want to count, I finally was able to get the same results across different date ranges. So I then took those rules and put them into another inline TVF that would give me back the correct offset for the specific date and time zone only to be used in a CROSS APPLY:
CREATE FUNCTION [dbo].[tvf_at_time_zone]
(
@thedate DATETIME,
@timeZoneID VARCHAR(60)
)
RETURNS TABLE
AS RETURN (
WITH Rules AS (
SELECT
DST.[ID],
DST.[DisplayName],
DST.[StandardName],
DST.[HasDST],
DST.[UTCOffsetHours],
DST.[UTCOffsetMinutes],
ISNULL(DST.[DeltaMinutes],0) AS DeltaMinutes,
ISNULL(DATEADD(ms, DATEDIFF(ms, 0, CAST(DST.BeginsTimeOfDay AS TIME)), CAST(startdim.TheDate AS DATETIME)), '1753-01-01 00:00:00.000') AS StartDateTime,
ISNULL(DATEADD(ms, DATEDIFF(ms, 0, CAST(DST.EndsTimeOfDay AS TIME)), CAST(enddim.TheDate AS DATETIME)), '9999-12-31 00:00:00.000') AS EndDateTime
FROM [dbo].[TimeZoneInfoNew] AS DST
LEFT OUTER JOIN dbo.DateDimension AS startdim
ON (startdim.TheDate >= DST.RuleStartDate AND
CASE
WHEN EXISTS (SELECT 1
FROM dbo.DateDimension AS dd
WHERE dd.TheDate >= DST.RuleStartDate AND
dd.TheDayOfWeekInMonth = DST.BeginsWeekOfMonth AND
dd.TheMonth = DST.BeginsMonthOfYear AND
dd.TheDayOfWeek = DST.BeginsDayOfWeek AND
dd.TheDate <=DST.RuleEndDate)
THEN startDim.TheDayOfWeekInMonth
ELSE startDim.TheWeekOfMonth
END = DST.BeginsWeekOfMonth AND
startdim.TheMonth = DST.BeginsMonthOfYear AND
startdim.TheDayOfWeek = DST.BeginsDayOfWeek AND
startdim.TheDate <=DST.RuleEndDate)
LEFT OUTER JOIN dbo.DateDimension AS enddim
ON (enddim.TheDate >= startdim.TheDate AND
CASE
WHEN EXISTS (SELECT 1
FROM dbo.DateDimension AS dd
WHERE dd.TheDate >= startdim.TheDate AND
dd.TheDayOfWeekInMonth = DST.EndsWeekofMonth AND
dd.TheMonth = DST.EndsMonthOfYear AND
dd.TheDayOfWeek = DST.EndsDayOfWeek AND
dd.TheYear = CASE
WHEN (DST.BeginsMonthOfYear > DST.EndsMonthOfYear)
THEN startdim.TheYear+1
ELSE startdim.TheYear
END AND
dd.TheDate <= CASE
WHEN DST.BeginsMonthOfYear > DST.EndsMonthOfYear AND DST.RuleEndDate < '9999/12/01'
THEN DATEADD(YYYY,1,DST.RuleEndDate)
ELSE DST.RuleEndDate
END)
THEN enddim.TheDayOfWeekInMonth
ELSE enddim.TheWeekOfMonth
END = DST.EndsWeekOfMonth AND
enddim.TheMonth = DST.EndsMonthOfYear AND
enddim.TheDayOfWeek = DST.EndsDayOfWeek AND
enddim.TheYear = CASE
WHEN (DST.BeginsMonthOfYear > DST.EndsMonthOfYear)
THEN startdim.TheYear+1
ELSE startdim.TheYear END AND
enddim.TheDate <= CASE WHEN DST.BeginsMonthOfYear > DST.EndsMonthOfYear AND DST.RuleEndDate < '9999/12/01' THEN DATEADD(YYYY,1,DST.RuleEndDate) ELSE DST.RuleEndDate END
)
),
AllDates AS (
SELECT *
FROM [dbo].[DateDimension] AS d
CROSS JOIN (SELECT DISTINCT [ID], [DisplayName], [StandardName], [UTCOffsetHours], [UTCOffsetMinutes]
FROM Rules
WHERE [ID] = @timeZoneID OR
[DisplayName] = @timeZoneID OR
[StandardName] = @timeZoneID) AS n
)
SELECT DISTINCT d.[ID] AS [name],
CASE WHEN COALESCE(r.[UTCOffsetHours], d.[UTCOffsetHours]) > 0 THEN '+' ELSE '-' END + LEFT(
CONVERT(VARCHAR(5),
DATEADD(
mi, -1*ISNULL(r.[DeltaMinutes],0),
DATEADD(mi, ABS(COALESCE(r.[UTCOffsetMinutes], d.[UTCOffsetMinutes])),
DATEADD(hh, ABS(COALESCE(r.[UTCOffsetHours], d.[UTCOffsetHours])), '1900/01/01')
)), 108), 5) AS [current_utc_offset],
CASE WHEN r.DeltaMinutes > 0 THEN 1 ELSE 0 END AS [is_currently_dst]
FROM AllDates AS d
LEFT OUTER JOIN Rules AS r ON d.StandardName = r.StandardName
AND d.TheDate >= r.StartDatetime AND d.TheDate < r.EndDateTime
WHERE @thedate = d.TheDate
)
The Results
SELECT OrderDate AT TIME ZONE 'Eastern Standard Time' AT TIME ZONE 'Pacific Standard Time' AS OrderDate,
COUNT(SalesOrderID) AS OrderCount
FROM [AdventureWorks2014].[Sales].[SalesOrderHeaderEnlarged]
GROUP BY OrderDate AT TIME ZONE 'Eastern Standard Time' AT TIME ZONE 'Pacific Standard Time'
ORDER BY OrderDate;
SELECT SWITCHOFFSET(TODATETIMEOFFSET(OrderDate, orig.theDateUTCOffSet), new.theDateUTCOffSet) AS OrderDate,
COUNT(SalesOrderID) AS OrderCount
FROM Sales.[SalesOrderHeaderEnlarged]
CROSS APPLY dbo.tvf_at_time_zone(OrderDate, 'Pacific Standard Time') AS new
CROSS APPLY dbo.tvf_at_time_zone(OrderDate, 'Eastern Standard Time') AS orig
GROUP BY SWITCHOFFSET(TODATETIMEOFFSET(OrderDate, orig.theDateUTCOffSet), new.theDateUTCOffSet)
ORDER BY OrderDate;
Since the DATETIME for the OrderDate is not at any given time zone the first query uses AT TIME ZONE to set it to Eastern Standard Time and then shift the time to Pacific Standard Time. I did this intentionally since the database could be inside of an AG where secondary replicas are not in the source time zone and this would have effects to the output without first setting the source time zone to Eastern Standard Time. If the data type is not a datetimeoffset with the time zone information set, AT TIME ZONE assumes the data is at the local time zone of the instance of SQL Server. One thing I noticed while testing this is the performance is incrementally worse the more times you use AT TIME ZONE in a query. The query time stats for the above two queries were:
QueryTimeStats CpuTime=”424379″ ElapsedTime=”424935″
QueryTimeStats CpuTime=”24912″ ElapsedTime=”25135″
Compare this to the following query, which achieves the same results as the first query, since the time zone of my instance is already at Eastern Standard Time, but if your instance is at a different time zone this will have a different result:
SELECT OrderDate AT TIME ZONE 'Pacific Standard Time' AS OrderDate,
COUNT(SalesOrderID) AS OrderCount
FROM [AdventureWorks2014].[Sales].[SalesOrderHeaderEnlarged]
GROUP BY OrderDate AT TIME ZONE 'Pacific Standard Time'
ORDER BY OrderDate;
QueryTimeStats CpuTime=”243549″ ElapsedTime=”243826″
This is an important consideration, and you could bypass this by using the TODATETIMEOFFSET() function to provide the correct offset for the dates, but across ranges of dates where this could shift it would not work.
Making it Faster?
If you want to make this faster still, the information could be persisted as an additional table with appropriate indexing to allow rapid lookup of the offsets in a TVF.
USE [AdventureWorks2014]
GO
DROP TABLE IF EXISTS [dbo].[TimeZoneDSTDimension];
GO
CREATE TABLE [dbo].[TimeZoneDSTDimension](
[ID] [varchar](31) NOT NULL,
[DisplayName] [varchar](61) NOT NULL,
[StandardName] [varchar](31) NOT NULL,
[TheDate] [date] NOT NULL,
[theDateUTCOffSet] [varchar](6) NULL,
[IsDSTDate] [bit] NOT NULL,
[theDateUTCOffSetHours] [smallint] NULL,
[theDateUTCOffSetMinutes] [smallint] NULL,
--[DSTStart] [datetime] NULL,
--[DSTEnd] [datetime] NULL,
CONSTRAINT PK_TimeZoneDSTDimension PRIMARY KEY CLUSTERED (TheDate, ID)
) ON [PRIMARY]
GO
WITH Rules AS (
SELECT
DST.[ID],
DST.[DisplayName],
DST.[StandardName],
DST.[HasDST],
DST.[UTCOffsetHours],
DST.[UTCOffsetMinutes],
ISNULL(DST.[DeltaMinutes],0) AS DeltaMinutes,
ISNULL(DATEADD(ms, DATEDIFF(ms, 0, CAST(DST.BeginsTimeOfDay AS TIME)), CAST(startdim.TheDate AS DATETIME)), '1900-01-01 00:00:00.000') AS StartDateTime,
ISNULL(DATEADD(ms, DATEDIFF(ms, 0, CAST(DST.EndsTimeOfDay AS TIME)), CAST(enddim.TheDate AS DATETIME)), '2099-12-31 00:00:00.000') AS EndDateTime
FROM [dbo].[TimeZoneInfoNew] AS DST
LEFT OUTER JOIN dbo.DateDimension AS startdim
ON (startdim.TheDate >= DST.RuleStartDate AND
CASE
WHEN EXISTS (SELECT 1
FROM dbo.DateDimension AS dd
WHERE dd.TheDate >= DST.RuleStartDate AND
dd.TheDayOfWeekInMonth = DST.BeginsWeekOfMonth AND
dd.TheMonth = DST.BeginsMonthOfYear AND
dd.TheDayOfWeek = DST.BeginsDayOfWeek AND
dd.TheDate <=DST.RuleEndDate)
THEN startDim.TheDayOfWeekInMonth
ELSE startDim.TheWeekOfMonth
END = DST.BeginsWeekOfMonth AND
startdim.TheMonth = DST.BeginsMonthOfYear AND
startdim.TheDayOfWeek = DST.BeginsDayOfWeek AND
startdim.TheDate <=DST.RuleEndDate)
LEFT OUTER JOIN dbo.DateDimension AS enddim
ON (enddim.TheDate >= startdim.TheDate AND
CASE
WHEN EXISTS (SELECT 1
FROM dbo.DateDimension AS dd
WHERE dd.TheDate >= startdim.TheDate AND
dd.TheDayOfWeekInMonth = DST.EndsWeekofMonth AND
dd.TheMonth = DST.EndsMonthOfYear AND
dd.TheDayOfWeek = DST.EndsDayOfWeek AND
dd.TheYear = CASE
WHEN (DST.BeginsMonthOfYear > DST.EndsMonthOfYear)
THEN startdim.TheYear+1
ELSE startdim.TheYear
END AND
dd.TheDate <= CASE
WHEN DST.BeginsMonthOfYear > DST.EndsMonthOfYear AND DST.RuleEndDate < '9999/12/01'
THEN DATEADD(YYYY,1,DST.RuleEndDate)
ELSE DST.RuleEndDate
END)
THEN enddim.TheDayOfWeekInMonth
ELSE enddim.TheWeekOfMonth
END = DST.EndsWeekOfMonth AND
enddim.TheMonth = DST.EndsMonthOfYear AND
enddim.TheDayOfWeek = DST.EndsDayOfWeek AND
enddim.TheYear = CASE
WHEN (DST.BeginsMonthOfYear > DST.EndsMonthOfYear)
THEN startdim.TheYear+1
ELSE startdim.TheYear END AND
enddim.TheDate <= CASE WHEN DST.BeginsMonthOfYear > DST.EndsMonthOfYear AND DST.RuleEndDate < '9999/12/01' THEN DATEADD(YYYY,1,DST.RuleEndDate) ELSE DST.RuleEndDate END
)
),
AllDates AS (
SELECT *
FROM [dbo].[DateDimension] AS d
CROSS JOIN (SELECT DISTINCT [ID], [DisplayName], [StandardName], [UTCOffsetHours], [UTCOffsetMinutes]
FROM Rules) AS n
)
INSERT INTO dbo.TimeZoneDSTDimension
SELECT DISTINCT d.[ID], d.[DisplayName], d.[StandardName] AS [name],
TheDate,
CASE
WHEN COALESCE(r.[UTCOffsetHours], d.[UTCOffsetHours]) > 0 THEN '+'
ELSE '-'
END +
LEFT(CONVERT(VARCHAR(5),
DATEADD(
mi, -1*ISNULL(r.[DeltaMinutes],0),
DATEADD(mi, ABS(COALESCE(r.[UTCOffsetMinutes], d.[UTCOffsetMinutes])),
DATEADD(hh, ABS(COALESCE(r.[UTCOffsetHours], d.[UTCOffsetHours])), '1900/01/01')
)), 108), 5) AS [TheDateUTCOffset],
CASE WHEN r.DeltaMinutes > 0 THEN 1 ELSE 0 END AS IsDSTDate,
CAST(PARSENAME(REPLACE(LEFT(CONVERT(VARCHAR(5),
DATEADD(
mi, -1*ISNULL(r.[DeltaMinutes],0),
DATEADD(mi, ABS(COALESCE(r.[UTCOffsetMinutes], d.[UTCOffsetMinutes])),
DATEADD(hh, ABS(COALESCE(r.[UTCOffsetHours], d.[UTCOffsetHours])), '1900/01/01')
)), 108), 5), ':', '.'), 2) AS INT) AS theDateUTCOffSetHours,
CAST(PARSENAME(REPLACE(LEFT(CONVERT(VARCHAR(5),
DATEADD(
mi, -1*ISNULL(r.[DeltaMinutes],0),
DATEADD(mi, ABS(COALESCE(r.[UTCOffsetMinutes], d.[UTCOffsetMinutes])),
DATEADD(hh, ABS(COALESCE(r.[UTCOffsetHours], d.[UTCOffsetHours])), '1900/01/01')
)), 108), 5), ':', '.'), 1) AS INT) AS theDateUTCOffSetMinutes
FROM AllDates AS d
LEFT OUTER JOIN Rules AS r ON d.StandardName = r.StandardName
AND d.TheDate >= r.StartDatetime AND d.TheDate < r.EndDateTime
GO
DROP FUNCTION IF EXISTS [dbo].[tvf_at_time_zone_persisted];
GO
CREATE FUNCTION [dbo].[tvf_at_time_zone_persisted]
(
@thedate DATETIME,
@timeZoneID VARCHAR(60)
)
RETURNS TABLE
AS RETURN (
SELECT *
FROM [dbo].[TimeZoneDSTDimension]
WHERE ID = @TimeZoneID AND TheDate = @thedate
)
GO
SELECT SWITCHOFFSET(TODATETIMEOFFSET(OrderDate, orig.theDateUTCOffSet), new.theDateUTCOffSet) AS OrderDate,
COUNT(SalesOrderID) AS OrderCount
FROM Sales.[SalesOrderHeaderEnlarged]
CROSS APPLY dbo.tvf_at_time_zone_persisted(OrderDate, 'Pacific Standard Time') AS new
CROSS APPLY dbo.tvf_at_time_zone_persisted(OrderDate, 'Eastern Standard Time') AS orig
GROUP BY SWITCHOFFSET(TODATETIMEOFFSET(OrderDate, orig.theDateUTCOffSet), new.theDateUTCOffSet)
ORDER BY OrderDate
The test query above has a slightly faster execution time by a few seconds across my tests, but isn’t significant over the inline TVF even with the Missing Index the plan recommends created.
QueryTimeStats CpuTime=”21529″ ElapsedTime=”21534″
Missing Rules Applied by AT TIME ZONE
There are still a couple of missing rules from AT TIME ZONE’s implementation that exist around the way that datetime values that fall into the gap where the time shift happens are handled. The details of the handling of those is documented in the REMARKS section of the Books Online. Personally I already spent way more time on this than I ever wanted to, so I didn’t even attempt going down that rabbit hole. Perhaps a challenge for another post at some point, but the purpose of the original post was to demonstrate the performance impact of AT TIME ZONE on large data sets. Hopefully this was useful to someone else, and I personally learned more than I ever possibly wanted to know about time zones and the effects of daylight savings time on data.
Summary
I am sure there is probably something I got wrong, but if I did, hopefully this is a big jump in the right direction and redeems the gross oversight of the original post missing the explanation that just using sys.time_zone_info statically for ranges of dates will not produce the correct result or offsets historically.
2 thoughts on “More About AT TIME ZONE”