Mysql：将 DB 从本地时间转换为 UTC
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2689428/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Mysql: Convert DB from local time to UTC
提问by Joernsn
I need to convert an existing (datetime fields) db from local time ut UTC.
我需要从本地时间 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).
这些值存储在时区 CET (+1)（夏令时 +2）的服务器上的广告日期时间。在选择我使用的数据时UNIX_TIMESTAMP()，它神奇地补偿了所有内容，即时区偏移和 dst（如果我已经正确阅读了文档）。
I'm moving the db to a new server with UTC as system time.
我正在将数据库移动到以 UTC 作为系统时间的新服务器。
Simply subtracting -1 H won't work, as summer time is +2.
简单地减去 -1 H 是行不通的，因为夏令时是 +2。
Any ideas for a clever way to do this? (using sql or some script lang)
有什么聪明的方法可以做到这一点吗？（使用 sql 或一些脚本语言）
回答by Ike Walker
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:
首先，您需要确保填充了 mysql.time_zone_name 表。如果它是空的，您可以按照此页面上的说明来填充它：
http://dev.mysql.com/doc/refman/5.1/en/time-zone-support.html
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:
它通常就像在 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:
填充该表后，您可以使用 CONVERT_TZ() 函数更新数据库中的现有值：
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_convert-tz
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:
这里有两个例子来展示它如何在冬季和夏季将日期时间从 CET 转换为 UTC：
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)
回答by Sven
It should be noted that the conversion for dates from one timezone to another or to UTC can only be done reliably if the dates are in the past.
应该注意的是，从一个时区到另一个时区或到 UTC 的日期转换只有在日期是过去的情况下才能可靠地完成。
Timezone definitions change. They are a human definition of how to deviate from the "sun clock", and those definitions can and do change constantly. So the only valid conversion is for dates in the past, because that will not change anymore.
时区定义发生变化。它们是人类对如何偏离“太阳钟”的定义，这些定义可以而且确实在不断变化。所以唯一有效的转换是针对过去的日期，因为它不会再改变了。
Any date in the future cannot reliably be converted, because the conversion can only take into account the currently known timezone definition.
未来的任何日期都无法可靠地转换，因为转换只能考虑当前已知的时区定义。
Simple example: Let's create a meeting appointment next year in Berlin, Germany. We agree today that we want to meet at 12:00 on July 1st, 2014 at Alexanderplatz. That date would be translated to 10:00 UTC on that day.
简单示例：让我们明年在德国柏林创建一个会议约会。我们今天同意，我们希望在 2014 年 7 月 1 日 12:00 在亚历山大广场见面。该日期将转换为当天 10:00 UTC。
Now if some government decides to opt out of daylight saving time in 2014, you'd have a problem deciding whether you should show up at 12:00 local time, or at 11:00 local time, because the conversion back from UTC will result in a different local time.
现在，如果某些政府决定在 2014 年选择退出夏令时，您将在决定是否应该在当地时间 12:00 或当地时间 11:00 出现时遇到问题，因为从 UTC 转换回来会导致在不同的当地时间。
If you had saved the original date of "2014-07-01 12:00 Europe/Berlin", you will be there at that exact time at noon, like everyone else.
如果您保存了“2014-07-01 12:00 Europe/Berlin”的原始日期，那么您将像其他人一样在中午的那个确切时间到达那里。
回答by Antonio Ca?as Vargas
In the original server, you can use one of the following expressions inside an UPDATE query:
在原始服务器中，您可以在 UPDATE 查询中使用以下表达式之一：
CONVERT_TZ(your_datetime_field,'SYSTEM','UTC')CONVERT_TZ(your_datetime_field,@@global.time_zone,'UTC')
CONVERT_TZ(your_datetime_field,'SYSTEM','UTC')CONVERT_TZ(your_datetime_field,@@global.time_zone,'UTC')
Alternatively, in the destination server, if you know the time zone of the original server (for example 'Europe/Berlin') you can use one of the following expressions:
或者，在目标服务器中，如果您知道原始服务器的时区（例如“欧洲/柏林”），您可以使用以下表达式之一：
CONVERT_TZ(your_datetime_field,'Europe/Berlin','UTC')CONVERT_TZ(your_datetime_field,'Europe/Berlin',@@global.time_zone)
CONVERT_TZ(your_datetime_field,'Europe/Berlin','UTC')CONVERT_TZ(your_datetime_field,'Europe/Berlin',@@global.time_zone)
