Need help on Daylight Savings Rule

[Md. Irfan]

We have an MCR solution to manage emails from customers. Our agents are distributed in groups and work in different time zones like CST, MST, PST, etc. The Genesys and database servers are located in CST zone.

We have a requirement wherein we have to check the email arrival date against a list of predefined holiday list in a custom table. If the date matches then we delay the processing of the email till the next working day else continue with normal processing. For this we have written a stored procedure which gets the email arrival date either from the UCS.INTERACTION table or from the UCS.EMAILIN table searching for the Interaction ID. Now the date returned also consists of time value along with it which is in GMT. And considering the difference between various Zones (GMTCST, GMTMST, GMTPST, etc.) being in the range of hours, the date changes when this timing is around midnight of the above timezones CST, PST etc.

For this we convert the date obtained from the UCS.INTERACTION or the UCS>EMAILIN tables to the date in respective time zones by using the "select newtime" function and then check the converted date against the holiday list. This is also used to calculate the Age of the Email going by the formula

Age of Email = Current Time in Zone Genesys Start time (email arrival time)

The Age of the Email is required to determine which stage of targeting the email should be subjected to.

This is working fine. However, there is a problem of one hour difference in the year when the Daylight Saving Rules are applied and then taken off in a year. We have to check whether the date falls between the period when the Daylight Savings Rule is applicable and accordingly add an extra hour to make the necessary adjustments. Because then the CST becomes CDT and the PST becomes PDT, etc. In the routing strategy we don't have the CDT and PDT zones to handle with.

At the moment we have hard coded the dates for year 2006 inside the stored procedure to handle this situation. However, this requires that someone has to update the stored procedure manually every year with the correct dates. This increases the dependency of the ssytem and is not an automatic change to avoid any human error.

If anyone who has faced this kind of situation where you have to play around with the Daylight Savings Rule and do the manipulation accordingly, please let me know the best way to handle this

[Kevin]

A couple quick thoughts
1 Instead of manually coding the dates in the stored proc, build a table that the proc referred to, and enter the DST start and end dates in it. The proc could then check to see if current date is between those two dates. Then, instead of having to change the proc each year, you only need to add records to (or update) the table.

2 In addition to #1 above, you might be able to add the Time Zone adjustment to GMT to the record, then the stored proc will use that in the calculations. If you passed the location (or time zone) as a parameter to the proc, you might be able to use it across any time zone.

3 Instead of converting from GMT to local time, would there be any benefit of converting from local to GMT?

4 Are there any OSlevel or database utilities or functions that you can utilize to do the conversion for you?

Just a few thoughts from a 5minute analysis

[Md. Irfan]

Hi Kevin,

Thanks for your Quick analysis.

1. I liked your point of having a table to store the dates for the Daylight Saving Period. That way we can avoid changing the stored procedure. However, this method still requires human intervention in the sense someone has to update the date records in the table. Isn't there any automatic way to update this table?

2. We are already passing the TimeZone as a variable and doing the conversion for various time zones in a single generalised stored procedure.

3. The reason we are doing conversion from GMT to local is that the table which contains the Holiday list is in local time zone. besides there is a possibility that the offices in different time zones have holidays on different days.

4. I am not sure of any database or OS level utilities that can do the conversion.

I know you must be very busy with your other important assignments. I want to thank you for taking out some time and analyzing my question and replying to it with some good suggestions.

Would be highly obliged if you can take out some more time and have a closer look at the requirements that I have listed in my original post as well as the updates in this post and share your opinion on the same.

Thanks & Regards,
Md. Irfan

[Kevin]

Another 5minute analysis:

While the table would require user intervention, entries could at least be put in years in advance, since the dates follow a specific rule, so you could compute those and enter them. Problem is, if 5 or 6 years down the road, you forgot to enter the new dates, you've got a problem.

Following that thought, since DST follows a specific rule (in 2006 in the U.S., spring ahead the first Sunday of April and fall back the last Sunday of October), you might be able to code the stored proc to perform the calculations to determine when the specific DST dates would be.

AN alternative would be to create the DST calculation logic in a separate, "seeding" stored proc that would perform the calculations and populate the table referred to previously. This way, the calculations are not performed every time the processing stored proc is run (thereby improving performance). If necessary, you may need to create a table that drives which DSTcalculation rule a specific time zone follows. Though the rules do not change often, it would at least permit updating of the time zones without changing the proc.

Depending on the OSs available, you could create a script that would execute the SQL statements to run the seeding proc, that would run once a month or so it would check to see if any DST dates existed after the current date. If not, it would populate the table. This solution would get around the user intervention aspect.

I had suggested the OS level utilities because, if I recall properly, the C language had some functions that would permit conversion to and from GMT. IF memory serves, Perl is based on C, but I do not know if these functions where carried through to Perl. Problem would be, calling the script from within the routing... I don't know if it is possible to make an external call to a script from within a DBMS, but it may be worth investigating.

Of course, there is always the Custom Server option, but other than knowing it is an option, I can't provide any guidance towards implementing it as a solution.