Joernsn Joernsn - 4 months ago 35
MySQL Question

Mysql: Convert DB from local time to UTC

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

, 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)


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:

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:

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)