We use Elastic Jobs to run overnight housekeeping scripts. These scripts need to run at a specific time during the night.
That's where this pesky thing called Daylight Saving, Summer Time or whatever it's called in your part of the world throws a minor spanner in the works.
To configure a job to start at a certain time we run
EXEC jobs.sp_update_job <etcera>, @schedule_start_time=<datetime in UTC>
The problem is that that will only work for part of the year. For example, here in Ireland we are currently on BST, i.e. UTC+1, so if I want to run this job at 06:00 every morning we would have to call the above with the @schedule_start_time parameter set to 05:00
This means that if we want to keep running this job all year round at 06:00 we need to run the sp_update_job statement at the time the clocks change. Which is not a big deal but a pain in the proverbial all the same.
So my question is:
Is there a way to call sp_update_job with a @schedule_start_time parameter that is interpreted as a local time so that it automatically adjusts when Daylight Savings starts or finishes?
Unfortunately, Elastic Jobs use UTC time zone for scheduling and do not automatically adjust for daylight savings. Therefore, as you already mentioned, you need to manually update the @schedule_start_time parameter of the sp_update_job procedure when the clocks change.
However, you have an alternative Azure service named Azure Automation that support time zones and adjust to daylight saving based on the schedule's time zone. Here you will find more details.
$TimeZone = ([System.TimeZoneInfo]::Local).Id
New-AzAutomationSchedule -AutomationAccountName "ContosoAutomation" -Name "Schedule01" -StartTime "23:00" -OneTime -ResourceGroupName "ResourceGroup01" -TimeZone $TimeZone