Steven Weintraubhttp://blogs.dovetailsoftware.com/blogs/sweintraub/atom.aspxCommunity Server2007-01-30T11:16:00ZAMDOCS/Clarify CRM and the issue of daylight saving time.http://blogs.dovetailsoftware.com/blogs/sweintraub/archive/2007/02/07/amdocs-clarify-crm-and-the-issue-of-daylight-saving-time.aspx2007-02-07T13:11:00Z2007-02-07T13:11:00Z<p><For those who have read this blog before, please notice we are now providing the solution below.></p><p>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.<br></p>
<p>Potentially this change will have greater effect than the y2k bug. Why? Two reasons. </p>
<p>First the y2k bug was known and advertised years ahead so many software people could make great livings fixing it before it hit. <br>With this daylight saving time change, many people are just becoming aware of it several weeks before it takes effect.</p>
<p>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.</p>
<p>This means all your software packages must be fixed to know the new rules.<br><br>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.</p>
<p>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.<br></p>
<p>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 : <br>
</p><pre><a href="http://www.dovetailsoftware.com/download/dst/dst.zip" class="moz-txt-link-freetext">http://www.dovetailsoftware.com/download/dst/dst.zip</a></pre>
<p>This zip file contains 4 files : daylightsaving.diet.dat, daylightsaving.dataex.mssql.sql, daylightsaving.dataex.oracle.sql, daylightsaving.dataex.dat.</p><p>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.</p><p>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.<br><span style="font-weight:bold;"></span></p><div style="margin-left:160px;"><span style="font-weight:bold;"> Daylight Saving Time Change From 2007 to 2049</span></div><blockquote><blockquote><blockquote><blockquote><table bgcolor="grey">
<tr>
<td> year </td>
<td> start time </td>
<td> stop time </td>
</tr>
<tr>
<td> 2007 </td>
<td> 03/11/2007 02:00:00 </td>
<td> 11/04/2007 02:00:00 </td>
</tr>
<tr>
<td> 2008 </td>
<td> 03/09/2008 02:00:00 </td>
<td> 11/02/2008 02:00:00 </td>
</tr>
<tr>
<td> 2009 </td>
<td> 03/08/2009 02:00:00 </td>
<td> 11/01/2009 02:00:00 </td>
</tr>
<tr>
<td> 2010 </td>
<td> 03/14/2010 02:00:00 </td>
<td> 11/07/2010 02:00:00 </td>
</tr>
<tr>
<td> 2011 </td>
<td> 03/13/2011 02:00:00 </td>
<td> 11/06/2011 02:00:00 </td>
</tr>
<tr>
<td> 2012 </td>
<td> 03/11/2012 02:00:00 </td>
<td> 11/04/2012 02:00:00 </td>
</tr>
<tr>
<td> 2013 </td>
<td> 03/10/2013 02:00:00 </td>
<td> 11/03/2013 02:00:00 </td>
</tr>
<tr>
<td> 2014 </td>
<td> 03/09/2014 02:00:00 </td>
<td> 11/02/2014 02:00:00 </td>
</tr>
<tr>
<td> 2015 </td>
<td> 03/08/2015 02:00:00 </td>
<td> 11/01/2015 02:00:00 </td>
</tr>
<tr>
<td> 2016 </td>
<td> 03/13/2016 02:00:00 </td>
<td> 11/06/2016 02:00:00 </td>
</tr>
<tr>
<td> 2017 </td>
<td> 03/12/2017 02:00:00 </td>
<td> 11/05/2017 02:00:00 </td>
</tr>
<tr>
<td> 2018 </td>
<td> 03/11/2018 02:00:00 </td>
<td> 11/04/2018 02:00:00 </td>
</tr>
<tr>
<td> 2019 </td>
<td> 03/10/2019 02:00:00 </td>
<td> 11/03/2019 02:00:00 </td>
</tr>
<tr>
<td> 2020 </td>
<td> 03/08/2020 02:00:00 </td>
<td> 11/01/2020 02:00:00 </td>
</tr>
<tr>
<td> 2021 </td>
<td> 03/14/2021 02:00:00 </td>
<td> 11/07/2021 02:00:00 </td>
</tr>
<tr>
<td> 2022 </td>
<td> 03/13/2022 02:00:00 </td>
<td> 11/06/2022 02:00:00 </td>
</tr>
<tr>
<td> 2023 </td>
<td> 03/12/2023 02:00:00 </td>
<td> 11/05/2023 02:00:00 </td>
</tr>
<tr>
<td> 2024 </td>
<td> 03/10/2024 02:00:00 </td>
<td> 11/03/2024 02:00:00 </td>
</tr>
<tr>
<td> 2025 </td>
<td> 03/09/2025 02:00:00 </td>
<td> 11/02/2025 02:00:00 </td>
</tr>
<tr>
<td> 2026 </td>
<td> 03/08/2026 02:00:00 </td>
<td> 11/01/2026 02:00:00 </td>
</tr>
<tr>
<td> 2027 </td>
<td> 03/14/2027 02:00:00 </td>
<td> 11/07/2027 02:00:00 </td>
</tr>
<tr>
<td> 2028 </td>
<td> 03/12/2028 02:00:00 </td>
<td> 11/05/2028 02:00:00 </td>
</tr>
<tr>
<td> 2029 </td>
<td> 03/11/2029 02:00:00 </td>
<td> 11/04/2029 02:00:00 </td>
</tr>
<tr>
<td> 2030 </td>
<td> 03/10/2030 02:00:00 </td>
<td> 11/03/2030 02:00:00 </td>
</tr>
<tr>
<td> 2031 </td>
<td> 03/09/2031 02:00:00 </td>
<td> 11/02/2031 02:00:00 </td>
</tr>
<tr>
<td> 2032 </td>
<td> 03/14/2032 02:00:00 </td>
<td> 11/07/2032 02:00:00 </td>
</tr>
<tr>
<td> 2033 </td>
<td> 03/13/2033 02:00:00 </td>
<td> 11/06/2033 02:00:00 </td>
</tr>
<tr>
<td> 2034 </td>
<td> 03/12/2034 02:00:00 </td>
<td> 11/05/2034 02:00:00 </td>
</tr>
<tr>
<td> 2035 </td>
<td> 03/11/2035 02:00:00 </td>
<td> 11/04/2035 02:00:00 </td>
</tr>
<tr>
<td> 2036 </td>
<td> 03/09/2036 02:00:00 </td>
<td> 11/02/2036 02:00:00 </td>
</tr>
<tr>
<td> 2037 </td>
<td> 03/08/2037 02:00:00 </td>
<td> 11/01/2037 02:00:00 </td>
</tr>
<tr>
<td> 2038 </td>
<td> 03/14/2038 02:00:00 </td>
<td> 11/07/2038 02:00:00 </td>
</tr>
<tr>
<td> 2039 </td>
<td> 03/13/2039 02:00:00 </td>
<td> 11/06/2039 02:00:00 </td>
</tr>
<tr>
<td> 2040 </td>
<td> 03/11/2040 02:00:00 </td>
<td> 11/04/2040 02:00:00 </td>
</tr>
<tr>
<td> 2041 </td>
<td> 03/10/2041 02:00:00 </td>
<td> 11/03/2041 02:00:00 </td>
</tr>
<tr>
<td> 2042 </td>
<td> 03/09/2042 02:00:00 </td>
<td> 11/02/2042 02:00:00 </td>
</tr>
<tr>
<td> 2043 </td>
<td> 03/08/2043 02:00:00 </td>
<td> 11/01/2043 02:00:00 </td>
</tr>
<tr>
<td> 2044 </td>
<td> 03/13/2044 02:00:00 </td>
<td> 11/06/2044 02:00:00 </td>
</tr>
<tr>
<td> 2045 </td>
<td> 03/12/2045 02:00:00 </td>
<td> 11/05/2045 02:00:00 </td>
</tr>
<tr>
<td> 2046 </td>
<td> 03/11/2046 02:00:00 </td>
<td> 11/04/2046 02:00:00 </td>
</tr>
<tr>
<td> 2047 </td>
<td> 03/10/2047 02:00:00 </td>
<td> 11/03/2047 02:00:00 </td>
</tr>
<tr>
<td> 2048 </td>
<td> 03/08/2048 02:00:00 </td>
<td> 11/01/2048 02:00:00 </td>
</tr>
<tr>
<td> 2049 </td>
<td> 03/14/2049 02:00:00 </td>
<td> 11/07/2049 02:00:00 </td>
</tr>
<tr></tr>
</table></blockquote></blockquote></blockquote></blockquote><img src="http://blogs.dovetailsoftware.com/aggbug.aspx?PostID=210" width="1" height="1">sweintraubhttp://blogs.dovetailsoftware.com/members/sweintraub.aspxYou got to think like your usershttp://blogs.dovetailsoftware.com/blogs/sweintraub/archive/2007/01/30/you-got-to-think-like-your-users.aspx2007-01-30T16:16:00Z2007-01-30T16:16:00Z<p><a href="http://blogs.dovetailsoftware.com/blogs/mburpo/default.aspx" id="ctl00___ctl00___ctl00_ctl00_bcr_BlogTitle" class="BlogTitle">Melissa Burpo</a> in her blog <a href="http://blogs.dovetailsoftware.com/blogs/mburpo/archive/2007/01/26/don-t-lose-your-users-in-abstraction.aspx" title="Don't lose your users in abstraction" target="_blank">"Don't lose your users in abstraction"</a> talks about actually making people to fit your abstract users. But you have to think like your users to actually make full use of this.</p><p> Two examples popped up this last week which shows you why you need to think like the concrete users you make for the abstractions. </p><p>First happened just yesterday when discussing test cases on users. The test writer was writing a test case that email addresses should be unique throughout the system. This may be true for<span style="font-weight:bold;"> '</span>the Administrator', 'The Agent' and 'The Customer - Dr. Ortho', but may not be true for 'The customer - Patricia Patient'. We all know people who are not sophisticated net users who have one Email address for the whole family (probably your parents). Patricia Patient's husband Paul might also be a customer of the company and they share an email address. Once we stepped back and discussed this fact - it became apparent that we had to think like end customers to avoid putting in a constraint that all contact email addresses must be unique.<br></p><p>Another example came up in a discussion of a third company (which I don't have the solid reference for) who put a constraint in their database that two people from the same family can't be born in the same month of the same year. I think the problems with this is obvious. But what happen was some programmer was trying to put a fraud detection into their system - and coded what worked without thinking what the real world was. When the first family with twins joined the system - oh no. The agent had to enter the twins as being born in different months.</p><p>So beyond trying to make your abstract users concrete - you also have to interact with the system as those users would. And every design decision needs to take into account everyone one of these users. If you don't think like them, you're likely to make code that locks them out of the system.<br> </p><img src="http://blogs.dovetailsoftware.com/aggbug.aspx?PostID=188" width="1" height="1">sweintraubhttp://blogs.dovetailsoftware.com/members/sweintraub.aspx