Mysql:将数据库从本地时间转换为UTC [英] Mysql: Convert DB from local time to UTC

问题描述

我需要从本地时间ut UTC转换现有的(日期时间字段)数据库.

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

这些值将广告日期时间存储在时区为CET(+1)(夏令时为+2)的服务器上.选择数据时，我使用UNIX_TIMESTAMP()，它神奇地补偿了所有内容，即时区偏移和dst(如果我正确阅读了文档).

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

我要将数据库移至以UTC作为系统时间的新服务器.

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

简单地减去-1 H无效，因为夏令时为+2.

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

有什么聪明的方法可以做到这一点吗? (使用sql或某些脚本lang)

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

推荐答案

首先，您需要确保已填充mysql.time_zone_name表.如果为空，则可以按照此页面上的说明进行填充:

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

通常就像在shell中运行这样的命令一样简单:

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

填充该表后，您可以使用CONVERT_TZ()函数更新数据库中的现有值:

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

以下是两个示例，展示了冬季和夏季如何将日期时间从CET转换为UTC:

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)

这篇关于Mysql:将数据库从本地时间转换为UTC的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！