What time is it in Oz?

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';

Timezones suck

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

Thanks to 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;

Saving & sharing

I’ve added the table & view to my DBA Database, and the PowerShell installer will populate it. It’s easy to install, if you want to take it for a spin to use in your environment.