![]() Haystack uses the zoneinfo database, which is also used by Unix and many programming languages. ![]() Luckily, there is a database that keeps track of these rules across regions and time. This means that computing a date in 2000 uses different rules than 2010 (we call these historical timezones). For example, in 2007 the US changed the dates for when DST starts and ends. This means that a given region might change its timezone rules (either UTC offset of DST rules) over time. Timezones are quite problematic for computers because they are a political abstraction versus a scientific abstraction. But between 2am on the second Sunday of March and 2am on the first Sunday in November is daylight savings time (DST) and is -4hrs from UTC.īecause timezones are such a critical aspect of time-series data, Haystack requires a comprehensive model and database of timezones. For example, US Eastern Standard Time is -5hrs from UTC. In Haystack, we use the term timezone to encapsulate two concepts: offset from UTC and daylight saving time rules. Timezones are identified using the tz tag with a city name from the zoneinfo database (discussed below). This makes it critical to define a proper, unambiguous representation of time and timezones. It’s easy to install, if you want to take it for a spin to use in your environment.Time-series data is the foundation for sensor and operational data. ![]() I’ve added the table & view to my DBA Database, and the PowerShell installer will populate it. JOIN sys.time_zone_info s ON s.name = tz.TimeZoneId WHEN s.is_currently_dst = 1 THEN tz.DaylightName 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:ĬREATE OR ALTER VIEW dbo.TimeZoneDetailed 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.” In particular, the DMV tells me what time zones are currently experiencing DST. This way, I can get all rich display info, as well as the limited info provided in the DMV. Time to create a view to combine my table with the data in sys.time_zone_info. Write-SqlTableData -ServerInstance $instance -Database $DatabaseName -SchemaName "dbo" -Table "TimeZones" -InputData $TimeZoneInfo Select-Object TimeZoneId, DisplayName, StandardName, DaylightName, SupportsDaylightSavingTime $TimeZoneInfo = Get-TimeZone -ListAvailable |Īdd-Member -MemberType AliasProperty -Name TimeZoneId -Value Id -PassThru | Here’s my table:ĬONSTRAINT PK_TimeZones PRIMARY KEY CLUSTERED (TimeZoneId)Īnd then a few lines of PowerShell, and I can dump the output of Get-TimeZone into that table: Now that I have the data, I can dump that info a table. Thanks to Get-Help Get-TimeZone, I then found that Get-TimeZone -ListAvailable gave me everything I needed. I opened up a PowerShell prompt, typed Get-TimeZone and BOOM□, it returned my current time zone, complete with pretty descriptions:ĭisplayName : (UTC-05:00) Eastern Time (US & Canada) The AT TIME ZONE syntax won’t be able to use my table, but at least it would give me better info to use. 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. 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. The info in that DMV is pretty “sparse”, if I’m being polite. To make it even more confusing, sys.time_zone_info abbreviates these differently making them even more difficult to sort through: There is “Australia Eastern Time”, which uses DST, and “Eastern Australia Time”, which does not use DST. It turns out that timezones are complicated in Australia. 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: As of this writing, Windows recognizes 141 different timezones, many of which overlap. ![]() Arizona Time doesn’t observe Daylight Saving Time, so sometimes it’s the same as Mountain Time, and sometimes it’s Pacific Time.įinding the right time zone can be hard. Let’s be honest though–timezones are confusing, especially when DST comes into play. There was a recent post on Database Administrators, where there was some confusion over Daylight Saving Time(DST) in Australian timezones. ![]()
0 Comments
Leave a Reply. |