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.
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
tzdatalinux 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
TimeZoneInfofrom .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:
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.
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.
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