Although a few posts have already been made about this, I thought I’d post a nice quick article by Peter Gulutzan and Dmitri Lenev on what to do with the upcoming daylight savings time changes and MySQL. This is in case you aren’t using the OS for your time zone info. You can find this out by issuing the following query:
SELECT @@global.time_zone;
If you get back “SYSTEM”, then the MySQL server is looking to the OS for timezone data, which is the default setting.
DAYLIGHT SAVING TIME CHANGES
By Peter Gulutzan and Dmitri Lenev
This year many American states and Canadian provinces will switch to daylight saving time on March 11 (the second Sunday of March) instead of switching on the first Sunday of April as happened during previous years.
If you populated the mysql.time_zone_transition according to the instructions in the MySQL Reference Manual, “5.11.8. MySQL Server Time Zone Support” now’s the time to make sure the time is up to date.
Here is an example, taken from an older system with the “wrong” dates. The region setting, that is, the value of @@time_zone, was ‘America/Edmonton’.
(The example is for a Canadian time zone because the risk is greatest there, but you should check for all the locations you work in.)
Find out the time zone id for the region:
mysql> select * from mysql.time_zone_name
-> where Name = 'America/Edmonton';
+------------------------+--------------+
| Name | Time_zone_id |
+------------------------+--------------+
| America/Edmonton | 100 |
+------------------------+--------------+
1 row in set (0.97 sec)
Find out the transition dates for that zone in this year:
mysql> select * from time_zone_transition
-> where Time_zone_id =
-> and Transition_time between 1167634800 and 1199170799
-> order by Time_zone_id,Transition_time;
+--------------+-----------------+--------------------+
| Time_zone_id | Transition_time | Transition_type_id |
+--------------+-----------------+--------------------+
| 100 | 1175418000 | 1 |
| 100 | 1193558400 | 2 |
+--------------+-----------------+--------------------+
2 rows in set (0.00 sec)
Find out what ‘1175418000′ and ‘1193558400′ mean:
mysql> select from_unixtime(1175418000);
+---------------------------+
| from_unixtime(1175418000) |
+---------------------------+
| 2007-04-01 03:00:00 |
+---------------------------+
1 row in set (0.00 sec)
mysql> select from_unixtime(1193558400);
+---------------------------+
| from_unixtime(1193558400) |
+---------------------------+
| 2007-10-28 01:00:00 |
+---------------------------+
1 row in set (0.00 sec)
Diagnosis: this database thinks the switch is on April 1, which is wrong. Cure: update your operating system files, check the “MySQL Server Time Zone Support” section of the manual, and update the table.