HOW TO: Handle Daylight Saving Time changes in Informatica Cloud (IICS)

1. Introduction

Daylight Saving Time(DST) is followed in many parts of the world where the clock is set forward and reverted back at a particular time in a year to make better use of day light.

When the clock springs forward and backward, there are certain issues observed in the Informatica jobs with the timezone conversion of datetime values and job schedules during the DST start and end times.

Let us discuss in detail about Daylight Saving Time and how to handle the Daylight Saving Time changes in Informatica Cloud (IICS).

2. What is Daylight Saving Time (DST)?

Daylight Saving Time (also called “Summer Time” in many parts of the world) is the process of setting the clock forward by one hour from the standard timezone time in spring and revert back by fall to make better use of natural day light.

Approximately 70 countries follow Daylight Saving Time currently. When DST is not observed, it is called standard time, normal time or winter time.

3. When Daylight Saving Time Starts and Ends?

DST is followed differently in different parts of world.

In most parts of United States and Canada, clocks change at 2:00 AM local time on the Second Sunday in March and reverts to standard time on the First Sunday in November.

In March, clocks spring forward from 1:59 AM to 3:00 AM
In November, clocks fall back from 1:59 AM to 1:00 AM

Here each time zone switches at a different time i.e. at 02:00 AM their local standard time.

In Europe, clocks change at 1:00 AM Universal Time (GMT) on last Sunday in March and reverts to standard time on the last Sunday in October.

In March, clocks spring forward from 12:59 AM to 2:00 AM
In October, clocks fall back from 1:59 AM to 1:00 AM

In the European Union, all time zones change at the same moment i.e. at 1:00 AMUniversal Time.

Daylight Saving Time also observed in other parts of the world like Australia, Brazil etc. and the start and end times are followed differently.

4. Impact on Date Time values processed by Informatica during DST changes

If you are working with data from different timezones, the conversion of datetime values from one timezone to other might cause some issues in Informatica.

For example, if you are working with data in PST timezone and have to convert it into EST timezone value, DST will not cause any issues in this case.

PST is 3 hours behind EST. As the both timezones follow Daylight Saving time, the difference remains same before and after the start of DST.

For example, if you are working with data in PST timezone and have to convert it into UTC timezone value, DST will cause issue in the time conversion as the PST observes the change in time whereas the UTC remains same.

PST is 8 hours behind Universal Time (UTC) when DST is not in effect. When the DST starts, PST is 7 hours behind the UTC.

In the initial example, the PST datetime value can be converted to EST datetime value by using the Informatica built-in function ADD_TO_DATE as below

DateTime_EST = ADD_TO_DATE(DateTime_PST, ‘HH24’, 3)

In the second example, when the DST is in effect use the below expression to convert PST datetime value to UTC datetime value.

DateTime_UTC = ADD_TO_DATE(DateTime_PST, ‘HH24’, 7)

When the DST ends, use the below Informatica expression to convert PST datetime value to UTC datetime value.

DateTime_UTC = ADD_TO_DATE(DateTime_PST, ‘HH24’, 8)

This is not recommended as we cannot keep changing the code accordingly when the Daylight Saving Time starts and ends.

5. Formula to calculate Daylight Saving Time changes in Informatica Cloud

Below is the formula to calculate Daylight Saving Time Start date and End date in Informatica Cloud for US and Canada regions.

DST_Start_Date = MAKE_DATE_TIME(GET_DATE_PART( DateTime_Value,’YYYY’),03,14 – mod((1+FLOOR(GET_DATE_PART( DateTime_Value,’YYYY’)*5/4)),7),2)

DST_End_Date = MAKE_DATE_TIME(GET_DATE_PART( DateTime_Value,’YYYY’),11,7 – mod((1+FLOOR(GET_DATE_PART( DateTime_Value,’YYYY’)*5/4)),7),2)

Below is the formula to calculate Daylight Saving Time Start date and End date in Informatica Cloud for European Union regions.

DST_Start_Date = MAKE_DATE_TIME(GET_DATE_PART( DateTime_Value,’YYYY’),03,31 – mod((4+FLOOR(GET_DATE_PART( DateTime_Value,’YYYY’)*5/4)),7),1)

DST_End_Date = MAKE_DATE_TIME(GET_DATE_PART( DateTime_Value,’YYYY’),10,31 – mod((1+FLOOR(GET_DATE_PART( DateTime_Value,’YYYY’)*5/4)),7),2)

Once the start and end datetime values are calculated, check if the datetime value you want to convert falls in the start and end datetime values range. Accordingly add the hours to convert to the desired timezone value.

Below is the example to convert systemtimestamp which is in PST to a UTC datetime value in expression transformation.

We are making use of MAKE_DATE_TIME function to form the Daylight Saving Time start date and end date.

The syntax of MAKE_DATE_TIME function is

MAKE_DATE_TIME(year, month, day, hour, minute, second, nanosecond)

In the above formula we have used,

• We are fetching the year value from the datetime field that needs to be converted using GET_DATE_PART function.
• As we know the occurrence of months in which Daylight Saving Time starts or ends, the month values have been hardcoded.
• The day of occurrence or completion of Daylight Saving Time is calculated using a formula.
• Since the hour of occurrence or completion is again a constant value every year, the value is hardcoded.
• The rest of the arguments after hour like minutes, seconds and nanoseconds are not passed to MAKE_DATE_TIME function. The output value of the expression will return these values as 00.

To understand better let us calculate the Daylight Saving Time start date for the year 2021. Below is how the formula translates for the day calculations in the formula

DST_Start_Date = MAKE_DATE_TIME(GET_DATE_PART( DateTime_Value,'YYYY'),03,14 - mod((1+FLOOR(GET_DATE_PART( DateTime_Value,'YYYY')*5/4)),7),2)

Year calculation:

GET_DATE_PART( DateTime_Value,'YYYY') 
GET_DATE_PART( '2021-01-01 00:00:00','YYYY') = 2021

Month value is hardcoded to 03

Day Calculation:

14 - mod((1+FLOOR(GET_DATE_PART(DateTime_Value,'YYYY')*5/4)),7)
14 - mod((1+FLOOR(GET_DATE_PART('2021-01-01 00:00:00','YYYY')*5/4)),7)
14 - mod((1+FLOOR((2021)*5/4)),7)
14 - mod((1+FLOOR(2526.25)),7)
14 - mod((1+2526),7)
14 - mod(2527,7)
14 – 0
14

Hour value is hardcoded to 02

The final formula translates as below which gives result as the 14^th of March is the Daylight Saving Time start date in the year 2021.

MAKE_DATE_TIME(2021,03,14,2) = 03/14/2021 02:00:00

6. Impact on Informatica jobs during Daylight Saving Time Start

In March as the clocks spring forward from 1:59 AM to 3:00 AM, the jobs that are scheduled between 2:00 to 2:59 are impacted.

These jobs do not run on the day Daylight Saving Time comes into effect and should be triggered manually after 3:00 AM.

In order to avoid Informatica Cloud skipping the scheduled run of the jobs, make sure there are no schedule jobs to run between 2:00 AM and 2:59 AM if possible.

7. Impact on Informatica jobs during Daylight Saving Time End

As the clock fall back from 1:59 AM to 1:00 AM when the Daylight Saving Time ends, the jobs that should be triggered at 2:00 am will not be effected but start with one hour delay than usual as the clock set back to 1:00 AM.

No effect is observed on jobs starting at 1:00 AM when clock sets back to 1:00 AM from 1:59 AM. Once the 1:00 am job completes running, the job will be scheduled to run at 1:00 am next day. Falling back to 1:00 AM again will not cause retriggering of job.

8. Conclusion

If you are working with time zones that are not effected by Daylight Saving Time changes or the time zones that are equally effected by Daylight Saving Time changes, it might not cause an issue in the data that you are processing from Informatica.

But if you are dealing with time zones in which one is effected by Daylight Saving Time and the other do not, then you should consider incorporating the formula to calculate the daylight saving start and end date to calculate the difference between time zones.

If your jobs are scheduled in time zones that are effected by Daylight Saving Time changes, make sure the jobs are not scheduled during the window where Daylight Saving Time changes take effect if possible.