Drupal Dates off by timezone offset, after upgrading date module

Just a quick note of how we resolved an issue related to an upgrade to Date.

We recently updated Date in our core distribution to date-7.x-2.9-beta1 and on one site, we had a number of date fields set up as just "Date". After the update, the display of all of these ended up 7 - 8 hours off -- when you edit the node, it shows up with the correct time, and when you display the node, it's off by 8 hours.

8 hours happens to be our current timezone offset from UTC, and 7 hours is the offset during Daylight Savings Time, so this immediately made me think it was a timezone offset problem.

Looking in the database, the field in question was storing time in a MySQL datetime column (which is conveniently easy to read, but does not specify time zone) and the times were all set to values that correspond to our local time, not UTC.

It's somewhat shocking to find > 1200 open issues on the Drupal.org queue for date module. This one seems most relevant: Problem with timezone handling (caused by date_get_timezone_db returning only UTC) (link is external). I completely agree with the Proper Timezone Handling (link is external) definition of how date data should get stored -- the only sane way is using UTC, and it should get converted in and out of the database to whichever local time the admin desires. And according to the first case, older versions (2.7) of date did store in UTC... but that's not what I'm seeing.

Normally I would file an issue, or find the right place to comment, but at the moment time is short and for this module it doesn't quite seem worthwhile -- especially because to me it looks like the date module is actually fixed -- this is mainly an issue of data not getting properly updated.

Fix the date field

So... bottom line, it looks to me like on this site at least, Date fields have stored times incorrectly in local time. With our update to 2.9-beta1, it now correctly translates time from UTC to local time on display -- but the widget for editing time is suddenly set to UTC. This is an easy fix in the field settings, changing field time zone handling from UTC to Site's time zone (see screen shot).

That fixes the field, and from this point forward, all new dates saved should get converted properly between local time in the editing screen and display, and UTC in the database.

However, now we have 651 nodes with the wrong time.

Fix the data

So now what we need to do is change the stored data for each affected date field. Turns out MariaDB/MySQL has a convenient "convert_tz()" function that can do just that. However, for this to be aware of daylight savings time rules, you need to populate the database with timezone data.

On a Linux server, this is a very quick operation, because the system already has timezone data. It just needs to be loaded in the database. You'll need your root password for this:

mysql_tzinfo_to_sql /usr/share/zoneinfo|mysql -u root -p mysql
After that, you can update each date field with a query. For a field called field_cutoff, the query looks like this:

update field_data_field_cutoff set field_cutoff_value = convert_tz(field_cutoff_value,"US/Pacific",'UTC') ;
So you'll need to set the table name and the field name containing the data to match your field, and you'll also need to find a timezone name that matches your location, with the appropriate timezone rules.

You'll probably need to clear the cache, but after that, you're all set! Dates fixed, daylight savings rules and all!

• John Locke

More info can be found on our blog http://www.freelock.com/freelock-blog