Recurring DST Anamolies

Daylight Saving Time is an obnoxious concept to deal with in the IT world.  At a minimum it means that early morning scheduled tasks need to be monitored twice per year to ensure they aren't skipped or run twice.  But if you have customers in other time zones DST can become a real nuisance that isn't easily handled using any current job scheduler.  

Way back in 1999 I was forced by my employer to work New Year's Eve, "just in case", due to the Y2K bug.  Neither our SQL Servers nor Oracle boxes had any problems.  It was a different story with our mainframes.  Management decided to have them powered down at 23:00 on New Year's Eve and powered back up at 01:00.  The problem was some of the hardware fried in the process.  I look back 15 years to that time with fondness because we only 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 YorkSydney Australia
Time Zone NameAmerican ESTAustralian EST
GMT/UTC OffsetGMT -5GMT +10
Hemisphere affect on DSTDST starts first Sunday in March, ends first Sunday in NovemberDST starts first Sunday in October, ends first Sunday in March
Current timeMarch 9, 2015 12:00 NoonMarch 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:

EventNew YorkSydney AustraliaAU is ahead of NY by...
First Sunday of March US adds DSTMarch 9, 2015 12:00 NoonMarch 10, 2015 0:300 (AM)15 hours
First Sunday of April AU removes DSTApril 6, 2015 12:00 NoonApril 7, 2015 02:00 (AM)14 hours
First Sunday of October AU adds DSTOctober 5, 2015 12:00 NoonOctober 6, 2015 04:00 (AM)15 hours
First Sunday of November US removes DSTNovember 9,2015 12:00 NoonNovember 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.