<For those who have read this blog before, please notice we are now providing the solution below.>
As many IT departments are just finding out, a major change is about to happen concerning clocks and the calendar. In late 2005 the Congress changed daylight saving time to last 5 weeks longer. Instead of starting the first week in April, daylight savings time starts the second Sunday in March. It will also end the first week in November. On top of that there are two extra problems. First the extension is not permanent - but indefinite. The Congress is saying sometime in the future - we don't know when - it will change back. Also Indiana has changed it rules of daylight saving time. Before now, certain Indiana counties did not participate in daylight saving time, now Indiana universally participates. Arizona still does not practice daylight saving time.
Potentially this change will have greater effect than the y2k bug. Why? Two reasons.
First the y2k bug was known and advertised years ahead so many software people could make great livings fixing it before it hit.
With this daylight saving time change, many people are just becoming aware of it several weeks before it takes effect.
Second is its effect. With y2k many of the affects were printing artifacts (years print 101 instead of 01 or fields not fitting on the screen). With this bug - unless it if fixed, times in your software will be off an hour for 3 weeks.
This means all your software packages must be fixed to know the new rules.
AMDOCS/Clarify CRM has an interesting twist on this problem. They were wise enough not to hardcode daylight saving time into their product. Instead they store it in the database. Unfortunately they provide no easy mechanism for updating the database table. The only way to correct it is going into the table with SQL, deleteing the now bad records and inserting corrected records. This requires a good knowledge of Clarify database setup and SQL. This is a problem. I am sure Clarify is providing a solution for those using Clarify support - but this does not help the many customers who have stopped paying support.
For those customers (and this blog post is for you) - they have to know how Clarify stores daylight saving time. There is a table daylight_hr that stores daylight saving start and stop time per year per timezone. First you need to delete the records for the USA and Canada timezones from 2007 on. Then you have to add an entry for each to each US and Canadian timezone (make sure you include 'Eastern Indiana Standard Time' but not 'Arizona Standard Time'). The times for the start and stop dates from now to 2049 are at the end of this post (for your other applications needing update). If you do this right you will delete 387 records and insert 473.
Dovetail Software is now providing free the fix for this problem. We have a set of four files to correct your database. You can download these files from :
http://www.dovetailsoftware.com/download/dst/dst.zip
This zip file contains 4 files : daylightsaving.diet.dat, daylightsaving.dataex.mssql.sql, daylightsaving.dataex.oracle.sql, daylightsaving.dataex.dat.
If you have our Archive Manager product (DIET) then the file 'daylightsaving.diet.dat' is a one pass solution. Simply import this file with the Archive Manager and it will delete the old time zone records and insert the new ones.
If you do not have our Archive Manager product, the correction is two steps. First you have to delete the old daylight saving records. With your favorite SQLrunner, run either 'daylightsaving.dataex.mssql.sql' or 'daylightsaving.dataex.oracle.sql' depending on whether you have a MSSQL or an Oracle database. Then you must import the new daylight saving records. Import the file 'daylightsaving.dataex.dat' using Clarify's dataex tool.
Daylight Saving Time Change From 2007 to 2049
| year |
start time |
stop time |
| 2007 |
03/11/2007 02:00:00 |
11/04/2007 02:00:00 |
| 2008 |
03/09/2008 02:00:00 |
11/02/2008 02:00:00 |
| 2009 |
03/08/2009 02:00:00 |
11/01/2009 02:00:00 |
| 2010 |
03/14/2010 02:00:00 |
11/07/2010 02:00:00 |
| 2011 |
03/13/2011 02:00:00 |
11/06/2011 02:00:00 |
| 2012 |
03/11/2012 02:00:00 |
11/04/2012 02:00:00 |
| 2013 |
03/10/2013 02:00:00 |
11/03/2013 02:00:00 |
| 2014 |
03/09/2014 02:00:00 |
11/02/2014 02:00:00 |
| 2015 |
03/08/2015 02:00:00 |
11/01/2015 02:00:00 |
| 2016 |
03/13/2016 02:00:00 |
11/06/2016 02:00:00 |
| 2017 |
03/12/2017 02:00:00 |
11/05/2017 02:00:00 |
| 2018 |
03/11/2018 02:00:00 |
11/04/2018 02:00:00 |
| 2019 |
03/10/2019 02:00:00 |
11/03/2019 02:00:00 |
| 2020 |
03/08/2020 02:00:00 |
11/01/2020 02:00:00 |
| 2021 |
03/14/2021 02:00:00 |
11/07/2021 02:00:00 |
| 2022 |
03/13/2022 02:00:00 |
11/06/2022 02:00:00 |
| 2023 |
03/12/2023 02:00:00 |
11/05/2023 02:00:00 |
| 2024 |
03/10/2024 02:00:00 |
11/03/2024 02:00:00 |
| 2025 |
03/09/2025 02:00:00 |
11/02/2025 02:00:00 |
| 2026 |
03/08/2026 02:00:00 |
11/01/2026 02:00:00 |
| 2027 |
03/14/2027 02:00:00 |
11/07/2027 02:00:00 |
| 2028 |
03/12/2028 02:00:00 |
11/05/2028 02:00:00 |
| 2029 |
03/11/2029 02:00:00 |
11/04/2029 02:00:00 |
| 2030 |
03/10/2030 02:00:00 |
11/03/2030 02:00:00 |
| 2031 |
03/09/2031 02:00:00 |
11/02/2031 02:00:00 |
| 2032 |
03/14/2032 02:00:00 |
11/07/2032 02:00:00 |
| 2033 |
03/13/2033 02:00:00 |
11/06/2033 02:00:00 |
| 2034 |
03/12/2034 02:00:00 |
11/05/2034 02:00:00 |
| 2035 |
03/11/2035 02:00:00 |
11/04/2035 02:00:00 |
| 2036 |
03/09/2036 02:00:00 |
11/02/2036 02:00:00 |
| 2037 |
03/08/2037 02:00:00 |
11/01/2037 02:00:00 |
| 2038 |
03/14/2038 02:00:00 |
11/07/2038 02:00:00 |
| 2039 |
03/13/2039 02:00:00 |
11/06/2039 02:00:00 |
| 2040 |
03/11/2040 02:00:00 |
11/04/2040 02:00:00 |
| 2041 |
03/10/2041 02:00:00 |
11/03/2041 02:00:00 |
| 2042 |
03/09/2042 02:00:00 |
11/02/2042 02:00:00 |
| 2043 |
03/08/2043 02:00:00 |
11/01/2043 02:00:00 |
| 2044 |
03/13/2044 02:00:00 |
11/06/2044 02:00:00 |
| 2045 |
03/12/2045 02:00:00 |
11/05/2045 02:00:00 |
| 2046 |
03/11/2046 02:00:00 |
11/04/2046 02:00:00 |
| 2047 |
03/10/2047 02:00:00 |
11/03/2047 02:00:00 |
| 2048 |
03/08/2048 02:00:00 |
11/01/2048 02:00:00 |
| 2049 |
03/14/2049 02:00:00 |
11/07/2049 02:00:00 |
|
