Normally when the extracts are scheduled to run daily at some time say 1AM EST, they will keep on running at 1AM. When Daylight Savings start, the extract suddenly starts running at 2AM instead of 1AM due to daylight savings.
All extracts will face this issue during the DST (Daylight Savings Time). There is no seeded feature or setting to modify this behavior. If the timing doesn’t matter much we can leave those schedules as-is as we plan the schedules a bit ahead of our expectation. However if there are some extracts which would for sure need to run at the same time due to a downstream system expecting it or vendor system expecting the file, then the only way is to cancel the existing schedule and re-create the schedule till the DST is over. once DST is over, we will need to reschedule again..
In order to overcome this approach, we can create a flow schedule formula which takes care of the DST automatically. There might be a need to change the formula based on the exact dates for each year.
Once the formula is created, it can be used while scheduling the extract and then the formula subtracts 1hr when the DST starts which makes the extract timing perfectly same as the timing before DST. Here it is assumed that the extract is being scheduled at EST timezone before the DST starts.
Flow Schedule Formula Logic:
/* Inputs */
INPUTS ARE SUBMISSION_DATE(DATE), SCHEDULED_DATE(DATE)
/* Truncate NEXT_SCHEDULED_DATE to start of SCHEDULE_DATE + 1. */
NEXT_SCHEDULED_DATE = TRUNC(ADD_DAYS(SCHEDULED_DATE, 1))
/*
* EDT runs from first Sunday in March to second Sunday in November.
* EST runs from second Sunday in November to first Sunday in March.
*/
/* Year for NEXT_SCHEDULED_DATE. */
YEAR = TO_CHAR(NEXT_SCHEDULED_DATE, 'YYYY')
/* Day of week for Sunday : 3rd January 2016 falls on a Sunday. */
THIRD_JANUARY_2016 = TO_DATE('2016-01-03', 'YYYY-MM-DD')
SUNDAY = TO_CHAR(THIRD_JANUARY_2016, 'DAY')
/* Get first Sunday in March. */
FIRST_SUNDAY_IN_MARCH = TO_DATE(YEAR||'-03-01', 'YYYY-MM-DD')
I = 0
WHILE (I < 7) LOOP
(
DAY = TO_CHAR(FIRST_SUNDAY_IN_MARCH, 'DAY')
IF (DAY = SUNDAY) THEN
(
EXIT
)
FIRST_SUNDAY_IN_MARCH = ADD_DAYS(FIRST_SUNDAY_IN_MARCH, 1)
I = I + 1
)
/* Get second Sunday in November : start loop at 8th November. */
SECOND_SUNDAY_IN_NOVEMBER = TO_DATE(YEAR||'-11-08', 'YYYY-MM-DD')
I = 0
WHILE (I < 7) LOOP
(
DAY = TO_CHAR(SECOND_SUNDAY_IN_NOVEMBER, 'DAY')
IF (DAY = SUNDAY) THEN
(
EXIT
)
SECOND_SUNDAY_IN_NOVEMBER = ADD_DAYS(SECOND_SUNDAY_IN_NOVEMBER, 1)
I = I + 1
)
/*
* For EST timezone, add 1 hour to NEXT_SCHEDULED_DATE (which is UTC) to make process run at same time
* of day as EDT timezone.
*/
OFFSET = 0
IF (NEXT_SCHEDULED_DATE < FIRST_SUNDAY_IN_MARCH OR
NEXT_SCHEDULED_DATE >= SECOND_SUNDAY_IN_NOVEMBER) THEN
(
OFFSET = OFFSET - 1/24
)
NEXT_SCHEDULED_DATE = ADD_DAYS(NEXT_SCHEDULED_DATE, OFFSET)
/* Returns */
RETURN NEXT_SCHEDULED_DATE
NOTE: This formula has been written for EST/EDT timezone only. If there is a requirement to create FF for CST/PST, then the dates need to be modified as per the dates on which the DST starts.
Note: The above FF will subtract 1hr from the next schedule date if the date falls under DST. Assuming that the schedule has been created before DST starts. If the schedule has been created during DST, then next schedule date should be added with 1hr (1/24) time.
Please do your due diligence before using this approach.
For BI Report schedules, there isn’t any option as we cannot use the Flow schedule FF to create BI Report schedules. We will have to manually modify the schedules in order to reflect the DST timings.
Hope this was a good read. Do you know of any other approaches to handle this daylight savings issue for scheduled extracts? If yes pleas let me know in the comments section.
Great solution! Have you heard of any other solutions for dealing with DST? This is the only I’ve ever seen!
The other way is to cancel the extracts and re-schedule them.. There isn’t any other alternative…