I’ve ranted about times, datetimes, and the like before. As most programmers & IT pros do, I loathe time zones with a mild passion. As one of my favorite #SQLCommunity members quips:
daylight savings time was actually created by a government works project to ensure that programmers could forever write tedious conditional logic in their date-based queries [and code].Bert Wagner
Thankfully, in SQL 2016, MS has somewhat heard our outcries and given us an easier way to convert things between time-zones in a somewhat sensible manner.
at time zonekeyphrase
- The system metadata table that drives it
- A good blog post that explains essentially what I’m about to (because it’s great to have different perspectives and unique voices!)
- Two StackOverflow answers (one, two) on the topic
- Bert’s succinct and fun post about the same topic, with video!
In fact, I’m not even going to repeat what 2 (and many more, I’m sure) people have already said. Go check out their posts above! But since you’re here, this is how I use it for my reporting environment.
To start with, the transactional data is in PST/PDT — i.e. Pacific Time with DST fluctuation. Yes, it’s horrible. No, I don’t know what happens to events or jobs at 2am on the “Fall Back” date, or between 2am and 3am on the “Spring Forward” date. No, I can’t change it right now. Stop whining.
Now, I have offices in Paris France, Hong Kong, and Beijing China. These are 3 different “time zones”, but only 2 different
offsets — China and Hong Kong are in the same bucket, namely, UTC +08:00. More on that later.
So I have my
OfficeLocation lookup table:
Office | TimeZone
Paris | 'Central Europe Standard Time'
Beijing | 'China Standard Time'
HK | 'China Standard Time'
(Again, see below for why we can’t call HK’s zone “Hong Kong Time” like most websites/APIs would assume.)
Now, the cool thing about this is, we can pull those strings into a variable, or use them straight from the table, to convert our PST/PDT times to the appropriate zone.
Here’s a variable example:
DECLARE @TimeZoneStr sysname; --"sysname" is just nvarchar(128)
SELECT @TimeZoneStr = TimeZone
WHERE Office = 'Paris'
DECLARE @MyTimeNow datetime = GETDATE();
DECLARE @TimeInParis datetime;
SELECT @TimeInParis = @MyTimeNow
AT TIME ZONE 'Pacific Standard Time' --Converts to datetimeoffset
AT TIME ZONE @TimeZoneStr --Shifts it to Paris time
PRINT ('The time now in Paris is ' + CONVERT(varchar(30), @TimeInParis, 121);
And here’s an example using the field straight from the table.
SELECT Office , [Time in my location] = GETDATE() , [Time in remote office location] = CONVERT(datetime, GETDATE() AT TIME ZONE 'Pacific Standard Time' AT TIME ZONE ol.TimeZone) FROM OfficeLocation ol
See the Gist for a full-fledged sample script. In it, I have a “transaction table” where I insert a few events for each office, with the ‘original date’ in PST, and I show how to easily convert that to each office’s local time, or visa-versa.
Here’s the major catch. The information available to YOUR instance of SQL Server is pulled from that server’s Windows Registry hive. No, I’m not making this up. So if that box doesn’t know about, say, ‘Hong Kong Standard Time’, and you try to use that in your SQL statement.. you’re hosed.
And yes, that is a real example from my own experience.
This article shows the “Windows standard format” time zone list. As you can see, they merged some zones with others because.. they felt like it? But apparently
Central Europe Standard Time,
Central European Standard Time, and
Romance Standard Time (all UTC +01:00) were completely necessary to keep separate. Go figure.
In my use-case above, then, I couldn’t actually store the string
Hong Kong Time, because my SQL instances (hence my Windows Servers) don’t know what that is. Thankfully, at least for this decade, it doesn’t look like Hong Kong and China will diverge in terms of their geopolitical directions, and we’re safe to assume that HKT = CST (China Standard Time, not to be confused with US Central Time!).
In another example, the typical go-to site for timezone questions says Japan observes “Japan Standard Time”. Obviously enough. But Microsoft, in their infinite wisdom, decided to call that “Tokyo Standard Time”. Go figure again.
It also kinda makes you wonder.. how does this work on SQL on Linux? No such thing as “the registry” there. I’m sure there’s an internal OS data-store that houses time-zone info, of course. Heck, they might even be better at it than Windows. But it makes you think.
If you’re not already running SQL 2016 or upward, this should give you yet another compelling reason to upgrade. Seriously.
And don’t do what I did and attempt to store a “business locations with time-zone offsets” table, that you have to remind yourself every 6 months to go update (manually), and will inevitably fail to do so, and will not support any sensible manner of long-term historical reporting.
More to the point, don’t try to implement dynamic time-zone logic and calendaring yourself, in general. Because trust me, you’re not gonna get it right. Use the built-in tools, use the community resources, and be smart.
That’s all for today! ❤