There was a recent post on Database Administrators, where there was some confusion over Daylight Saving Time(DST) in Australian timezones. Let’s be honest though–timezones are confusing, especially when DST comes into play. Arizona Time doesn’t observe Daylight Saving Time, so sometimes it’s the same as Mountain Time, and sometimes it’s Pacific Time.
Finding the right time zone can be hard. As of this writing, Windows recognizes 141 different timezones, many of which overlap.
In this case, the asker was running this query, and confused over why it wasn’t properly reflecting DST, and the other columns in the DMZ weren’t helpful either:
SELECT current_utc_offset FROM sys.time_zone_info WHERE [name] = 'E. Australia Standard Time';
It turns out that timezones are complicated in Australia. There is “Australia Eastern Time”, which uses DST, and “Eastern Australia Time”, which does not use DST. To make it even more confusing,
sys.time_zone_info abbreviates these differently making them even more difficult to sort through:
SELECT * FROM sys.time_zone_info WHERE name LIKE '%Aus%';
The info in that DMV is pretty “sparse”, if I’m being polite. Wouldn’t it be nice if it gave you the same descriptions that you get from Windows when you’re selecting the timezone for the OS? I was talking to Aaron Bertrand (blog|twitter) about this when I had a sudden idea.
Doesn’t that DMV just pull from the OS?
If SQL Server is pulling that from the OS, shouldn’t I be able to do the same thing? Then maybe I could get better info, and stuff it into a table to use. The
AT TIME ZONE syntax won’t be able to use my table, but at least it would give me better info to use.
I opened up a PowerShell prompt, typed
Get-TimeZone and BOOM💥, it returned my current time zone, complete with pretty descriptions:
Id : Eastern Standard Time DisplayName : (UTC-05:00) Eastern Time (US & Canada) StandardName : Eastern Standard Time DaylightName : Eastern Daylight Time BaseUtcOffset : -05:00:00 SupportsDaylightSavingTime : True
Get-Help Get-TimeZone, I then found that
Get-TimeZone -ListAvailable gave me everything I needed.
I have the data. Let’s load it.
Now that I have the data, I can dump that info a table. Here’s my table:
CREATE TABLE dbo.TimeZones ( TimeZoneId nvarchar(64), DisplayName nvarchar(64), StandardName nvarchar(64), DaylightName nvarchar(64), SupportsDaylightSavingTime bit, CONSTRAINT PK_TimeZones PRIMARY KEY CLUSTERED (TimeZoneId) );
And then a few lines of PowerShell, and I can dump the output of
Get-TimeZone into that table:
$TimeZoneInfo = Get-TimeZone -ListAvailable | Add-Member -MemberType AliasProperty -Name TimeZoneId -Value Id -PassThru | Select-Object TimeZoneId, DisplayName, StandardName, DaylightName, SupportsDaylightSavingTime Write-SqlTableData -ServerInstance $instance -Database $DatabaseName -SchemaName "dbo" -Table "TimeZones" -InputData $TimeZoneInfo
Time(zone) for a view
Time to create a view to combine my table with the data in
sys.time_zone_info. This way, I can get all rich display info, as well as the limited info provided in the DMV. In particular, the DMV tells me what time zones are currently experiencing DST. Another shortcoming of the DMV is that it always displays something like “Eastern Standard Time” even during DST when it is more accurately named “Eastern Daylight Time.”
With this view, I can get the alternate names, including the Display Name, and the “Daylight” name, and also get the “is currently DST” info, all in one place:
CREATE OR ALTER VIEW dbo.TimeZoneDetailed AS SELECT tz.TimeZoneId, tz.DisplayName, tz.StandardName, tz.DaylightName, tz.SupportsDaylightSavingTime, IsCurrentlyDst = s.is_currently_dst, CurrentUtcOffset = s.current_utc_offset, CurrentName = CASE WHEN s.is_currently_dst = 1 THEN tz.DaylightName ELSE tz.StandardName END FROM dbo.TimeZones tz JOIN sys.time_zone_info s ON s.name = tz.TimeZoneId;