had one Y2K issue back then. Now we have them twice a year.
The first Monday after Daylight Saving Time (DST) (and it is "Saving" not "Savings") changes is always fun when you work in IT. This happens at least twice a year. DST is sometimes called Summer Time outside of the US. Quick history: DST was first proposed about a hundred years ago to aid retailers and other "after-work" activities that could benefit from more sunlight. Since then various countries, states, and municipalities have observed DST, off and on. Some people believe DST is a government plot, others find it a mere inconvenience.
If you run a SQL Server it's definitely a huge problem. If you have jobs or processes scheduled to run between 01:00 and 0:300 during a DST change then you may find that the given job either runs twice (when DST is "turned off"...in November since 2007 in the US) or not at all (when DST is "enabled"...in March since 2007 in the US).
Depending on what your scheduled tasks do, that could be a critical problem for you. Every client and employer I've had for the past 15 years has mandated at least some form of on-call availability for their DBAs during these twice-yearly DST events. For instance, some DBAs need to work when we "spring ahead" to ensure no jobs are skipped by manually starting them.
For some reason it seems like we can't get DST correct and automated. I've seen DBAs that never schedule a recurring job between 1 and 3 AM. I've seen others who schedule their 2AM jobs to run at 1:59:59 to avoid problems. Others will schedule their tasks to never occur "on the hour", rather at "10,20, or half past" the hour. Each of these techniques work with varying success. YMMV.
A DST problem you may not even know you have
Even if you have your jobs scheduled in such a way to mitigate these problems, your customers may still be impacted without you even knowing it. Even though the US recognizes DST changes on the first Sunday in March and November, not every country recognizes that same schedule.
Example: I am writing this blog post on Monday, March 9, 2015 at Noon in New York. In London it is currently 16:00 (4PM). This makes perfect sense since London is GMT 0 and New York is GMT -5.
Wait a second, that makes no sense whatsoever. Shouldn't it be 5PM in London? Nope. GMT 0 recognizes DST starting the last Sunday in March and ends DST the last Sunday in October. So, thanks to DST, New York is really only 4 hours ahead of London...for the next 3 weeks.
Now check this out...suppose your server was in New York and your customer was in Sydney, Australia:
|New York||Sydney Australia|
|Time Zone Name||American EST||Australian EST|
|GMT/UTC Offset||GMT -5||GMT +10|
|Hemisphere affect on DST||DST starts first Sunday in March, ends first Sunday in November||DST starts first Sunday in October, ends first Sunday in March|
|Current time||March 9, 2015 12:00 Noon||March 10, 2015 03:00 (AM).|
Let's do the math. Sydney should be 15 hours ahead of New York (GMT -5 vs GMT +10). And that looks correct to me since Noon in New York is 15 hours behind 3AM in Sydney. But look what happens at other times of the year:
|Event||New York||Sydney Australia||AU is ahead of NY by...|
|First Sunday of March US adds DST||March 9, 2015 12:00 Noon||March 10, 2015 0:300 (AM)||15 hours|
|First Sunday of April AU removes DST||April 6, 2015 12:00 Noon||April 7, 2015 02:00 (AM)||14 hours|
|First Sunday of October AU adds DST||October 5, 2015 12:00 Noon||October 6, 2015 04:00 (AM)||15 hours|
|First Sunday of November US removes DST||November 9,2015 12:00 Noon||November 10, 2015 05:00 (AM)||16 hours|
Note that due to DST and hemispherical considerations that Sydney's time can be anywhere from 14-16 hours ahead of New York.
What's the point?
If your server is on New York time and you have contractual data delivery requirements with customers in other time zones, you may need some remediation to meet those commitments. DST, if it is even recognized at all by a given municipality, may be activated or deactivated differently based on the rules of that municipality. And those rules are not static. The US changed when it recognized DST in 2007. Russia recognized DST until 2011 when it moved to "permanent DST" (ie, no standard time) until 2014 when it eliminated DST altogether. For fact checkers: see this. MS even issued guidance for SQL Servers back in 2007 when DST rules were changed.
DST is much more than just making sure your SQL Server Agent jobs don't get skipped in March and run twice in November. There's much more to it if you have customers in other regions.
For years I've been looking for elegant, universal "patterns" to getting around DST and timezone anamolies. I've resigned myself to the realization that nothing exists. In the past I've tried to code interesting solutions using
tzoffset that works, but the solution is always very much tied to the specific business use case.
You have just read "[[Recurring DST Anamolies]]" on davewentzel.com. If you found this useful please feel free to subscribe to the RSS feed.
Dave Wentzel CONTENT
sql server other