Mysql: Convert DB from local time to UTC

Question

I need to convert an existing (datetime fields) db from local time ut UTC.

The values are stored ad datetimes on a server with time zone CET (+1) (with summertime +2). When selecting data I use UNIX_TIMESTAMP(), which magically compensates for everything, ie, time zone shift and dst (if i've read the docs right).

I'm moving the db to a new server with UTC as system time.

Simply subtracting -1 H won't work, as summer time is +2.

Any ideas for a clever way to do this? (using sql or some script lang)

Answer 1

First you need to make sure the mysql.time_zone_name table is populated. If it's empty, you can follow the instructions on this page to populate it:

http://dev.mysql.com/doc/refman/5.1/en/time-zone-support.html

It's 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 DB:

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_convert-tz

Here are two examples to show how it converts datetimes from CET to UTC in winter vs summer:

mysql> SELECT CONVERT_TZ('2010-01-22 12:00:00','CET','UTC');
+-----------------------------------------------+
| CONVERT_TZ('2010-01-22 12:00:00','CET','UTC') |
+-----------------------------------------------+
| 2010-01-22 11:00:00                           |
+-----------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT CONVERT_TZ('2010-07-22 12:00:00','CET','UTC');
+-----------------------------------------------+
| CONVERT_TZ('2010-07-22 12:00:00','CET','UTC') |
+-----------------------------------------------+
| 2010-07-22 10:00:00                           |
+-----------------------------------------------+
1 row in set (0.00 sec)