Twice last week, a developer wanted to convert the existing datetime values in a database to UTC. The datetime values were the default for the server, which was the US/Pacific time zone, which is subject to Daylight Saving Time changes. Both developers for both applications wanted to convert all the times to UTC, so there would not be any changes due to Daylight Saving Time, and asked me for an easy query to know which times should be changed by adding 7 hours and which times should have 8 hours added to them.
The good news is that MySQL has some built-in functionality to make this easier. You may know about the CONVERT_TZ() function in MySQL, and that you can use it in a query to convert times like this:
mysql> SELECT CONVERT_TZ(NOW(),'-8:00','-0:00');
+-----------------------------------+
| CONVERT_TZ(NOW(),'-8:00','-0:00') |
+-----------------------------------+
| 2012-11-16 20:07:24 |
+-----------------------------------+
1 row in set (0.00 sec)
However, that is not much of a help, because if you know the timezone offset you can just add the right number of hours:
mysql> SELECT NOW()+INTERVAL 8 HOUR;
+-----------------------+
| NOW()+INTERVAL 8 HOUR |
+-----------------------+
| 2012-11-16 20:08:35 |
+-----------------------+
1 row in set (0.00 sec)
The Easier Way
A much easier way would be to set up the MySQL timezone tables so you could run a query like:
mysql> SELECT CONVERT_TZ(NOW(),'US/Pacific','UTC');
+--------------------------------------+
| CONVERT_TZ(NOW(),'US/Pacific','UTC') |
+--------------------------------------+
| 2012-11-16 20:10:30 |
+--------------------------------------+
1 row in set (0.00 sec)
And just to prove that this does the proper time conversion, consider this same time, 2 weeks ago, before the Daylight Saving Time change:
mysql> SELECT CONVERT_TZ(NOW()-interval 14 day,'US/Pacific','UTC');
+------------------------------------------------------+
| CONVERT_TZ(NOW()-interval 14 day,'US/Pacific','UTC') |
+------------------------------------------------------+
| 2012-11-02 19:10:52 |
+------------------------------------------------------+
1 row in set (0.00 sec)
MySQL knows when to add 8 hours, and when to add 7 hours. Magic!
Well, not quite magic. MySQL can only do this if you give it the timezone information. Luckily, servers have that information, and there is a tool that ships with MySQL that converts this timezone information to the right tables. Just follow the instructions on this page to populate the timezone tables. Its typically as simple as running a command like this in the shell:
mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql
Once that table is populated you can use the CONVERT_TZ() function to update the existing values in the database, using the text values for time zones.
Note: If you are living somewhere with Daylight Saving Time or Summer Time, there may be several different choices for what text you use for the timezone. Make sure you know exactly what these timezones do. For example, PDT is Pacific Daylight Time, which is UTC-7. PST is Pacific Standard Time, which is UTC-8. US/Pacific is the name for the timezone that is PDT in the summer and PST in the winter, so if you wanted to automatically convert dates that might fall under either PDT or PST, you want to use the US/Pacific time zone.
Comments are closed.