15

I'm wondering how other people deal with this scenario.

What if you have a job scheduled to run at 1:30 am. In the autumn, when time changes, the hour of 1:00:00 to 1:59:59 repeats itself and so that job would run twice.

Could be Windows Task Scheduler, SQL Agent or any other scheduling tool. Most of these tools seem to be based on machine time, not UTC time. If I told it to run the job at UTC time each night, then I wouldn't have the duplicate hour issue.

NealWalters
  • 1,273
  • 7
  • 18
  • 39
  • 1
    I couldn't find anything more current but hopefully this sheds some light on the issue for you - http://support.microsoft.com/kb/325413 – joeqwerty Oct 30 '13 at 20:32
  • That's excellent, why not post in an answer? – NealWalters Oct 30 '13 at 20:52
  • Well it doesn't really give you a solution (at least not that I could discern from reading it) but I thought it would be helpful in understanding the issue. I'm happy to leave it as a comment. – joeqwerty Oct 30 '13 at 20:57

3 Answers3

15

Proper scheduling of future tasks by local time, taking into account time zones and daylight saving time, is a very complex subject. I've written about it before from a programming perspective on Stack Overflow here and here.

I'll summarize from a non-programming perspective:

  • Define your recurrence patterns by local time - not UTC. For example, if you set a daily alarm clock to wake you up at 8:00 AM every day, you don't want to wake up an hour early or an hour late after a daylight saving time transition. If I'm in the US Pacific time zone, I can't schedule for 4:00 PM UTC, because after the transition it would have to switch to 3:00 PM UTC to keep the same 8:00 AM local time.

  • Define the time zone that the "local" time represents. Don't assume that the server's local time zone is the same time zone that matters to the end user.

  • Project the local time to a UTC date and time for each occurrence that you want the event to fire.

    • You'll almost always do this for the next immediate occurrence, such that you can use the UTC clock to determine the real instant in time to run.

    • In some cases, you may want to also project the next several (or many) instances, such as the next 5 occurrences, or all occurrences for the next year. (This part is highly specific to the requirements of the application.)

  • Have a strategy in place (either fixed or configurable), of what to do for occurrences that fall at the time of a daylight saving time transition:

    • For the "spring forward" transition, there's a gap of missing local time when the occurrence might not exist. For example, in US Pacific Time, a daily task scheduled to run at 2:00 AM local time will not exist on March 9th, 2014. In most cases, you'll want to advance that time by the saving amount (usually 1 hour), so on that day it will run at 3:00 AM, but will be back to running at 2:00 AM in the next instance. (However, it's entirely possible that you will want a different strategy for this.)

    • For the "fall back" transition, there's an overlap of duplicated local time when the occurrence might exist twice. For example, in US Pacific Time, a daily task scheduled to run at 1:00 AM will have two possible times it could run at on November 2, 2014. In most cases, you'll want to run at the first occurrence of 1:00 AM PDT and skip the next occurrence of 1:00 AM PST of the same date. (But again, you might want a different strategy, such as running on the second occurrence, or running in both. YMMV)

  • Be prepared to recalculate all of your occurrence UTC times if you ever need to update your time zone data. The IANA/Olson TZDB puts out multiple updates every year because governments of the world change their minds all the time about their time zone offsets and daylight saving time rules. You cannot assume for any specific duration of time in the future that the rules won't change.

    • Be sure to subscribe for announcements of time zone data releases, and have a process for applying them to your systems and/or applications.

    • In a traditional corporate setting, this should be the responsibility of the I.T. Operations staff.

    • Depending on your environment, you might be getting this data through tzdata linux package updates, through Java JRE or tzupdater, or any number of other channels. Sometimes it's environment specific, and sometimes it's programming platform specific, such as the timezonedb PECL package for PHP, and many others.

    • Microsoft has it's own time zone data. On Windows, if you're using TimeZoneInfo from .NET (for example), you are using this data. Updates come from here, and are also pushed out via Windows Update automatically, so you should keep an eye out for those so you know when/if you need to recalculate.

  • With all of that understood, there is still a scenario where you would schedule just by UTC, and that is for ABSOLUTE future events. Examples:

    • A job that runs every X hours or every X minutes.

    • Sunrise start and stop times, or other astronomical phenomenon.

    • A time-sensitive security window, such as when transmitting sensitive information to another party at a prearranged time.


Windows Task Scheduler

Windows isn't necessarily doing the right thing. Pay attention to how you define the trigger:

Windows Task Scheduler

When you check the box labeled "Synchronize across time zones", then the task is scheduled by UTC only. (All times are still shown as local time, but are stored as UTC.) So this is for what I earlier called an "absolute" event.

When you leave that box unchecked, it's going to use the local time zone of the computer the code is running on. It doesn't give you any option to specify the time zone, so that's not a very good implementation IMHO.

I'm not exactly sure of it's DST behavior, but I will experiment and get back to you on that. It probably does what I described above, but not necessarily.


SQL Agent

The SQL Agent scheduler is even worse, in that it only lets you use local server time. Again, no time zones can be specified, and you can't specify UTC either.

It has been requested, but not accepted.

Matt Johnson-Pint
  • 439
  • 1
  • 3
  • 12
  • So bottom line, with the specific tools SQL Agent and Windows Task Scheduler, you have manual changes to make each time change, right? Or potentially, the code ran from the scheduler could re-check the UTC time, then possibly do a delay (in the fall), but no way to avoid the job not running in the Spring other than a manual change. Or, perhaps a script to actually reprogram the schedule? – NealWalters Nov 13 '13 at 17:07
  • I updated the answer with info about Windows Task Scheduler and SQL Agent. Neither are completely doing it right. If want to develop your own solution, you might want to look at [Quartz.net](http://quartznet.sourceforge.net/). – Matt Johnson-Pint Nov 13 '13 at 17:27
  • In the short term, you could use Windows Task Scheduler with that box checked to define your tasks to run at specific UTC times. But you'll probably want those to be one-time tasks that you build programmatically from your own code so you can take the rest into consideration. – Matt Johnson-Pint Nov 13 '13 at 17:35
  • Another great answer! – NealWalters Nov 13 '13 at 22:40
1

By not caring, generally.

Ask the question "so what if the task runs twice?"

Generally, it's not going to matter, so you don't need to do anything. If it would matter, the simplest solution is to move the job out of the hour impacted by the daylight savings time change.

HopelessN00b
  • 53,385
  • 32
  • 133
  • 208
  • 1
    If I didn't care, I wouldn't have asked. Some matter, some don't. At 2:00, we have jobs that extract file and sends it to our vendors. I don't think 2:00 will repeat. We are going to move to 2:05 just for added insurance. We have backups, smart index jobs, etc... but fortunately they are later. – NealWalters Oct 30 '13 at 20:26
  • 1
    @NealWalters I think its a fair point, to be fair to hopelessnoob: If it *doesn't* matter if a task runs twice then why worry. I don't know about you but I have plenty of things to worry about instead that need to be worried about without worrying about the things I don't need to worry about. If it *does* matter then you should already coding it to do some sanity checking **anyway**. Having said that, its certainly a good idea to avoid scheduling stuff to run at the exact point the clocks go back -- that's just asking for trouble. – Rob Moir Nov 12 '13 at 10:56
  • It's not my choice, we send extract files to airports at that time of the morning, and that's the time they want the file. We are using BizTalk, a message based system, so harder to double-check for things like this. It's a little disappointing that SQL scheduler and Windows task scheduler don't allow for a daily UTC time. – NealWalters Nov 13 '13 at 15:48
1

As you noted, the hour between 1AM and 2AM repeats at the end of DST; when the reverse change occurs (the start of DST) the times between 2AM and 3AM won't occur (and your job won't run). Your best options are going to be

  • run the job schedule to UTC
  • run the job at a time outside the changeover (12:59AM or 3AM)
Andrew
  • 7,772
  • 3
  • 34
  • 43