Mysql：将数据库从本地时间转换为UTC

Question

我需要从本地时间 ut UTC 转换现有的（日期时间字段）数据库。

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

我正在将数据库移动到使用 UTC 作为系统时间的新服务器。

简单地减去 -1 H 是行不通的，因为夏令时是 +2。

有什么聪明的方法来做到这一点吗？ （使用sql或一些脚本语言）

Answer 1

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

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

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

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql

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

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

这里有两个例子显示它如何在冬季和夏季将日期时间从 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)

Answer 2

应该注意的是，只有当日期是过去的日期时，才能可靠地将日期从一个时区转换为另一个时区或转换为 UTC。

时区定义发生变化。它们是人类对如何偏离“太阳钟”的定义，这些定义可以而且确实在不断变化。因此，唯一有效的转换是过去的日期，因为它不会再改变。

未来的任何日期都无法可靠地转换，因为转换只能考虑当前已知的时区定义。

简单的示例：让我们创建明年在德国柏林的会议约会。今天我们同意于 2014 年 7 月 1 日中午 12:00 在亚历山大广场见面。该日期将转换为当天 10:00 UTC。

现在，如果某些政府决定退出 2014 年夏令时，那么您将面临决定是应该在当地时间 12:00 还是在当地时间 11:00 出现的问题，因为从 UTC 转换回来会导致在不同的当地时间。

如果您保存了原始日期“2014-07-01 12:00 Europe/Berlin”，那么您将像其他人一样在中午的确切时间到达那里。

Answer 3

在原始服务器中，您可以在 UPDATE 查询中使用以下表达式之一：

CONVERT_TZ(your_datetime_field,'SYSTEM','UTC')
CONVERT_TZ(your_datetime_field,@@global.time_zone,'UTC')

或者，在目标服务器中，如果您知道原始服务器的时区（例如“欧洲/柏林”），则可以使用以下表达式之一：

CONVERT_TZ(your_datetime_field,'Europe/Berlin','UTC')
CONVERT_TZ(your_datetime_field,'欧洲/柏林',@@global.time_zone)