MySQL UNIX_TIMESTAMP() 存储为 UTC，夏令时即将到来。

Question

我的数据库服务器（运行MySql 5.5）设置为UTC，并且日期以无符号整数的形式存储在数据库中。该数据库主要用于存储在特定时间（exec_time）运行的任务。

我通过使用用户登录时区（此处为BST）在PHP中创建一个时间戳来插入任务。例如，我设置了一个任务在1351396800时运行，这是明天早上4点GMT。

我使用以下查询从数据库中提取任务：

SELECT * FROM tasks WHERE exec_time <= UNIX_TIMESTAMP();

当时钟在明天凌晨2点倒回一小时时，这个设置还可以使用吗？

更新：PHP成功地转换了日期。当PHP时区设置为Europe/Dublin（目前是BST）时，添加的两个事件分别在午夜12点和早上4点，存储如下：

mysql> select exec_time, FROM_UNIXTIME(exec_time) from tasks order by id desc limit 2;
+-------------+----------------------------+
| exec_time | FROM_UNIXTIME(exec_time) |
+-------------+----------------------------+
|  1351378800 | 2012-10-27 23:00:00        |
|  1351396800 | 2012-10-28 04:00:00        |

- Niall

Answer 1

简而言之，只要您的exec_time列具有时间戳数据类型，您就应该没问题。

没有明确的UNIX_TIMESTAMP列数据类型。有一个TIMESTAMP列数据类型。此数据类型的列值在从日期/时间字符串转换并从UTC（又名Z或Zulu时间，以前是格林威治标准时间）转换为客户端连接的时区时会自动转换。在从UTC转换为日期/时间字符串时，会将其转换为客户端连接的时区。

因此，如果您将exec_time列存储为，则应该能够使用您提出的子句：

  WHERE exec_time <= UNIX_TIMESTAMP()

这将起作用，因为服务器端处理您的exec_time值和UNIX_TIMESTAMP()函数调用的结果都是在UTC上处理的。您的exec_time值将以UTC格式存储。

如果您将exec_time存储为UNSIGNED INT或类似的数字数据类型，则在存储之前无法利用自动转换为UTC的功能。

您可以通过设置客户端连接时区来更改显示转换行为，如下所示：

  SET time_zone='SYSTEM'   /* for your system's local time */

或者

  SET time_zone='+0:00'   /* for UTC */

或者

  SET time_zone'America/New_York' /* or 'Europe/Vienna' or whatever */

一旦您执行了这些SET操作之一，请

 SELECT exec_time, FROM_UNIXTIME(exec_time) 

了解您的价值观如何存储在服务器端并进行翻译。

如果您想看看八天后会发生什么，请尝试这个：

 SELECT 3600*24*8+exec_time, FROM_UNIXTIME(3600*24*8+exec_time) 

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

- O. Jones

---

谢谢。我将它们存储为无符号整数。从明天开始，GMT时间等同于UTC时间，所以理论上我给出的4点钟示例应该没问题吧？在PHP中，我使用date_default_timezone_set('Europe/Dublin')设置时区，然后使用mktime($hour,$min,0,$month,$day,$year)将其实际插入到数据库中。 - Niall

GMT是UTC的旧称，与一年中的时间无关。你是指英国时间吗？您可以通过设置客户端time_zone='+0:00'然后按照我展示的方式查询来检查会发生什么。 - O. Jones

抱歉，是的，我指的是明天起BST = UTC。我的客户现在都使用BST作为他们的时区。我现在会尝试设置客户端的时区。谢谢。 - Niall

Mysql在12点和4点存储两个任务，相隔5小时，这表明在某个阶段（我认为是PHP），日期在存储为无符号整数之前没有被正确转换。感谢您的帮助。 - Niall

Answer 2

回答你的问题，这取决于时间字段的重要性以及服务器本地时间是否会更改。如果是UTC，则可能不会更改。

MySQL中的时间类型不具备时区感知能力。您将不得不自己实现时区，例如始终存储UTC时间戳/日期时间和一个单独的时区列，其中包含从+12到-12小时的间隔偏移量，用于为时区添加或减去UTC时间戳。

不幸的是，放置时区字段中的值以及检索调整为时区的时间戳所需的工作都由您自己完成。

如果切换到Postgres是一种选择，那么您可以始终使用Postgres提供的TIMESTAMP WITH TIMEZONE类型。

- GordonM

---

@OllieJones 你能详细说明一下吗？ - GordonM

由于您将“TIMESTAMP”拼写为“TIMESTANP”，所以是错误的。 - Lusitanian

这也是错误的，因为即使没有带时区的装饰，MySQL TIMESTAMP 项目也以时区感知的方式处理。 - O. Jones

@OllieJones：确定吗？带或不带夏令时处理？ - hakre