DST patch upcoming

[andydreisch]

Hi everyone, we want to make you aware of an upcoming patch for Sugar installations that will correct for the changed Daylight Savings Time (DST) this year.

Summary

The U.S. Congress passed the Energy Policy Act of 2005. For our purposes, the changes to the Uniform Time Act of 1966 are at issue. Instead of setting clocks ahead one hour on the first Sunday of April (April 1, 2007), clocks will now be set ahead on the second Sunday in March (March 11, 2007).

Similarly, clocks will now be set back on the first Sunday of November (November 4, 2007) rather than the last Sunday of October (October 28, 2007).

So, there are three additional weeks of DST in spring and one additional week in the fall, for a total of four additional weeks of DST.

Canada will also follow this rule although Mexico, which used to follow the U.S. rule, no longer will and will continue to honor the former DST dates.

What does this mean for my Sugar installation?

Sugar installations contain a file that maps time zones to rules regarding DST, such as when (or even if) DST is honored. This file is used to calculate offsets so that a user in California sees date/time values in PST (or PDT) even though the server may be physically located in the UK and the creator of the record in Manila.

The file (timezones.php) is statically created during the build process here at Sugar and is provided when Sugar is installed. The file currently is built with the expectation that DST will switch over according to the prior rules (April 1st). The patch will update this file to reflect the new rules (i.e., DST now ranging from March 11 to November 4).

What versions are being patched?

Sugar plans to patch these code branches: 4.0.1, 4.2.1, 4.5.0, and 4.5.1.

When will the patch be ready?

We anticipate the patch will be available within the next week.

How will I obtain the patch and install it?

The DST patch will be a standard patch made available through the update service. System administrators will see the red banner signifying the patch is available and can install it through the normal means.

Further, we anticipate making the updated timezones.php file available separately via SugarForge.

The Sugar patch assumes that the corresponding DST fix for the operating system has also been applied.

After I apply the patch will all my appointments be displayed correctly if they start within the affected periods?

No. Appointments created before the Sugar patch is applied may appear one hour behind in Sugar if the date falls within the March 11 to April 1 window and the user's timezone is affected by this change (i.e., users located in the U.S. and Canada).

Is there a way to make all appointments appear correctly in Sugar?

The sooner you apply the patch the better. Also, updating records that were created before the patch was applied will recalculate and correct the offset.

[jnassi]

The DST patches are now available. See this thread.

[elRey]

Is the DST issue JUST a display problem???
The data is stored correctly, it will just display a hour off during the time between 3/11 and 4/1, right?

Or will it also affect the storing of times when saved during 3/11 to 4/1?

Thanks,
Rey

[jnassi]

Is the DST issue JUST a display problem???
The data is stored correctly, it will just display a hour off during the time between 3/11 and 4/1, right?

Or will it also affect the storing of times when saved during 3/11 to 4/1?

Thanks,
Rey

The data is stored in a consistent GMT format in the database. Where the DST fix comes into play is in how the data is displayed to the user, according to their time zone setting.

[jmaxwell]

For those (like us) who dont' want the hassle of integrating a whole version patch upgrade just to fix this DST stuff, the only file that is relavent is the includes/timezones.php file. Everything else in the 4.5.0i (or 4.5.1a) patch can be ignored from what I can tell. They mention here they would post the timezones.php file, but I didn't see that done. So, for anyone who wants to upgrade the DST fix without worrying about dealing with merging the rest of the BS included in the patch, just copy the changes from the updated timezones.php file into place.

FWIW
-Ed

[andydreisch]

jmaxwell, the universal patch was listed in jnassi's post. It's copied here:

http://www.sugarforge.org/frs/downlo...7DSTUpdate.zip

Andy

[elRey]

SQL to update existing appointments and task for this year...

NOTE: this updates all data inculde data belonging to users that don't observe DST. User at your own risk.

 ## SQL to adjust times for appointments and tasks that are affected by the DST change
## sets all the times back one hour 
#------------------------
update
calls m 
set 
m.date_start = CASE WHEN m.time_start < '01:00:00' 
THEN SUBDATE(m.date_start, INTERVAL 1 DAY) 
ELSE m.date_start END, 
m.time_start = CASE WHEN m.time_start < '01:00:00' 
THEN ADDTIME(m.time_start, '23:00:00') 
ELSE SUBTIME(m.time_start, '01:00:00') END,
m.date_modified = DATE_ADD(NOW(), INTERVAL 5 HOUR) 
WHERE 1=1 
and m.deleted = 0 
and ((m.date_start > '2007-03-10'
and m.date_start < '2007-04-02')
OR (m.date_start > '2007-10-27'
and m.date_start < '2007-11-05'));

update
meetings m 
set 
m.date_start = CASE WHEN m.time_start < '01:00:00' 
THEN SUBDATE(m.date_start, INTERVAL 1 DAY) 
ELSE m.date_start END, 
m.time_start = CASE WHEN m.time_start < '01:00:00' 
THEN ADDTIME(m.time_start, '23:00:00') 
ELSE SUBTIME(m.time_start, '01:00:00') END, 
m.date_modified = DATE_ADD(NOW(), INTERVAL 5 HOUR) 
WHERE 1=1 
and m.deleted = 0 
and ((m.date_start > '2007-03-10'
and m.date_start < '2007-04-02')
OR (m.date_start > '2007-10-27'
and m.date_start < '2007-11-05'));

update
tasks m 
set 
m.date_start = CASE WHEN m.time_start < '01:00:00' 
THEN SUBDATE(m.date_start, INTERVAL 1 DAY) 
ELSE m.date_start END, 
m.time_start = CASE WHEN m.time_start < '01:00:00' 
THEN ADDTIME(m.time_start, '23:00:00') 
ELSE SUBTIME(m.time_start, '01:00:00') END, 
m.date_modified = DATE_ADD(NOW(), INTERVAL 5 HOUR) 
WHERE 1=1 
and m.deleted = 0 
and ((m.date_start > '2007-03-10'
and m.date_start < '2007-04-02')
OR (m.date_start > '2007-10-27'
and m.date_start < '2007-11-05'));

update
tasks m 
set 
m.date_due = CASE WHEN m.time_due < '01:00:00' 
THEN SUBDATE(m.date_due, INTERVAL 1 DAY) 
ELSE m.date_due END, 
m.time_due = CASE WHEN m.time_due < '01:00:00' 
THEN ADDTIME(m.time_due, '23:00:00') 
ELSE SUBTIME(m.time_due, '01:00:00') END, 
m.date_modified = DATE_ADD(NOW(), INTERVAL 5 HOUR) 
WHERE 1=1 
and m.deleted = 0 
and ((m.date_due > '2007-03-10'
and m.date_due < '2007-04-02')
OR (m.date_due > '2007-10-27'
and m.date_due < '2007-11-05'));
#------------------------

[kirkland]

From timezones.php:

'America/Los_Angeles' =>
array (
'gmtOffset' => -480,
'dstOffset' => 60,
'dstMonth' => 3,
'dstStartday' => 7,
'dstWeekday' => 0,
'stdMonth' => 10,
'stdStartday' => 0,
'stdWeekday' => 0,
'dstStartTimeSec' => 7200,
'stdStartTimeSec' => 3600,
),

Why is it that the America/Los_Angeles entry appears to suggest that the date starts on 3/7 and ends on 10/0? Not understanding these entries, it seems like the start date would be 3/11 and end 11/4.

[kvalley32]

Thanks for the sql elRey - That saved some searching this morning when that 1-hour-problem arose for us!
kary