文章/答案/技术大牛

发布

社区首页 >问答首页 >为什么MySQL在使用FROM_UNIXTIME时计算一个错误的时间戳

问为什么MySQL在使用FROM_UNIXTIME时计算一个错误的时间戳
EN

Database Administration用户

提问于 2017-01-15 23:43:23

回答 1查看 18.8K关注 0票数 8

我对此进行了大量的调试，并最终在mysql shell中复制了它。

我用字段类型timestamp将时间戳存储在mysql数据库中。我使用FROM_UNIXTIME()从脚本中更新它们，在选择它们时使用UNIX_TIMESTAMP()。

当我不使用SET time_zone =在连接中设置时区时，它可以正常工作。但是，当我设置一个时区时，会发生以下情况：

UNIX_TIMESTAMP()仍然给出了正确的结果。
UPDATE table SET field = FROM_UNIXTIME(..)在DB中设置了错误的值。
设置中的错误值不对应于服务器时区和连接时区之间的偏移量。服务器时区为亚洲/曼谷(UTC + 7)，连接时区为欧洲/柏林(UTC + 1)。但是，该值是以1小时差而不是6小时的方式存储的。
当我再次读取这个值时，我得到了错误的值。

我知道不工作的是FROM_UNIXTIME()，因为当我打开另一个没有连接特定时区的连接时，我会看到错误的值，直到我再次更新它。

事实上，这是一个小时的差异，让我认为，这可能是夏令时的问题。因为柏林有夏令时间，曼谷没有(据我所知)。

这是mysql的未经修改的日志，我在其中复制了这种行为。

服务器时区是亚洲/曼谷(CIT)。

$ mysql -uroot -p timezonetest
Enter password: 
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 530
Server version: 5.7.16-0ubuntu0.16.10.1 (Ubuntu)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SELECT @@system_time_zone;
+--------------------+
| @@system_time_zone |
+--------------------+
| ICT                |
+--------------------+
1 row in set (0.00 sec)

mysql> describe test;
+------------+-----------+------+-----+-------------------+-----------------------------+
| Field      | Type      | Null | Key | Default           | Extra                       |
+------------+-----------+------+-----+-------------------+-----------------------------+
| payment_id | int(11)   | NO   | PRI | NULL              | auto_increment              |
| begins_at  | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+------------+-----------+------+-----+-------------------+-----------------------------+
2 rows in set (0.00 sec)

mysql> select * from test;
+------------+---------------------+
| payment_id | begins_at           |
+------------+---------------------+
|     338840 | 2013-10-27 08:15:33 |
+------------+---------------------+
1 row in set (0.00 sec)

mysql> select unix_timestamp(begins_at) from test where payment_id = 338840;
+---------------------------+
| unix_timestamp(begins_at) |
+---------------------------+
|                1382836533 |
+---------------------------+
1 row in set (0.00 sec)

mysql> set time_zone = 'CET';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test;
+------------+---------------------+
| payment_id | begins_at           |
+------------+---------------------+
|     338840 | 2013-10-27 02:15:33 |
+------------+---------------------+
1 row in set (0.00 sec)

mysql> select unix_timestamp(begins_at) from test where payment_id = 338840;
+---------------------------+
| unix_timestamp(begins_at) |
+---------------------------+
|                1382836533 |
+---------------------------+
1 row in set (0.00 sec)

mysql> update test set begins_at = from_unixtime(1382836533) where payment_id = 338840;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select unix_timestamp(begins_at) from test where payment_id = 338840;
+---------------------------+
| unix_timestamp(begins_at) |
+---------------------------+
|                1382832933 |
+---------------------------+
1 row in set (0.00 sec)

mysql> select * from test;
+------------+---------------------+
| payment_id | begins_at           |
+------------+---------------------+
|     338840 | 2013-10-27 02:15:33 |
+------------+---------------------+
1 row in set (0.00 sec)

mysql>

另一个日志：

    mysql> describe test;
+------------+-----------+------+-----+-------------------+-----------------------------+
| Field      | Type      | Null | Key | Default           | Extra                       |
+------------+-----------+------+-----+-------------------+-----------------------------+
| payment_id | int(11)   | NO   | PRI | NULL              | auto_increment              |
| begins_at  | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+------------+-----------+------+-----+-------------------+-----------------------------+
2 rows in set (0.00 sec)

mysql> select * from test;
+------------+---------------------+
| payment_id | begins_at           |
+------------+---------------------+
|     338840 | 2013-10-27 02:15:33 |
+------------+---------------------+
1 row in set (0.00 sec)

mysql> set time_zone = 'Europe/Berlin';
Query OK, 0 rows affected (0.00 sec)

mysql> select unix_timestamp(begins_at) from test;
+---------------------------+
| unix_timestamp(begins_at) |
+---------------------------+
|                1382832933 |
+---------------------------+
1 row in set (0.00 sec)

mysql> update test set begins_at = from_unixtime(1382836533);
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> select unix_timestamp(begins_at) from test;
+---------------------------+
| unix_timestamp(begins_at) |
+---------------------------+
|                1382832933 |
+---------------------------+
1 row in set (0.00 sec)

mysql> select * from test;
+------------+---------------------+
| payment_id | begins_at           |
+------------+---------------------+
|     338840 | 2013-10-27 02:15:33 |
+------------+---------------------+
1 row in set (0.00 sec)

mysql> set time_zone = 'Asia/Bangkok';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test;
+------------+---------------------+
| payment_id | begins_at           |
+------------+---------------------+
|     338840 | 2013-10-27 07:15:33 |
+------------+---------------------+
1 row in set (0.00 sec)

mysql> select unix_timestamp(begins_at) from test;
+---------------------------+
| unix_timestamp(begins_at) |
+---------------------------+
|                1382832933 |
+---------------------------+
1 row in set (0.00 sec)

mysql> update test set begins_at = from_unixtime(1382836533);
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select unix_timestamp(begins_at) from test;
+---------------------------+
| unix_timestamp(begins_at) |
+---------------------------+
|                1382836533 |
+---------------------------+
1 row in set (0.00 sec)

mysql> select * from test;
+------------+---------------------+
| payment_id | begins_at           |
+------------+---------------------+
|     338840 | 2013-10-27 08:15:33 |
+------------+---------------------+
1 row in set (0.00 sec)

mysql>

mysql

timezone

timestamp

回答 1

Database Administration用户

回答已采纳

发布于 2017-01-16 02:01:03

MySQL的行为是正确的-你的测试无效。

如果您使用DST往返于时区，则如果遇到过渡，您将不会有无损的转换。所讨论的时间戳发生在"CET“和”欧洲/柏林“的DST过渡期间。

亚洲/曼谷有两个墙钟时间，相当于欧洲/柏林的一个墙钟时间。

mysql> SELECT CONVERT_TZ('2013-10-27 08:15:33','Asia/Bangkok','Europe/Berlin');
+------------------------------------------------------------------+
| CONVERT_TZ('2013-10-27 08:15:33','Asia/Bangkok','Europe/Berlin') |
+------------------------------------------------------------------+
| 2013-10-27 02:15:33                                              |
+------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT CONVERT_TZ('2013-10-27 07:15:33','Asia/Bangkok','Europe/Berlin');
+------------------------------------------------------------------+
| CONVERT_TZ('2013-10-27 07:15:33','Asia/Bangkok','Europe/Berlin') |
+------------------------------------------------------------------+
| 2013-10-27 02:15:33                                              |
+------------------------------------------------------------------+
1 row in set (0.00 sec)

把这个转换成UTC..。

mysql> select convert_tz('2013-10-27 02:59:59','Europe/Berlin','UTC');
+---------------------------------------------------------+
| convert_tz('2013-10-27 02:59:59','Europe/Berlin','UTC') |
+---------------------------------------------------------+
| 2013-10-27 00:59:59                                     |
+---------------------------------------------------------+
1 row in set (0.00 sec)

两秒钟后..。

mysql> select convert_tz('2013-10-27 03:01:01','Europe/Berlin','UTC');
+---------------------------------------------------------+
| convert_tz('2013-10-27 03:01:01','Europe/Berlin','UTC') |
+---------------------------------------------------------+
| 2013-10-27 02:01:01                                     |
+---------------------------------------------------------+
1 row in set (0.00 sec)

...it是一个小时零2秒后。

或者把它翻过来。

mysql> SET @@time_zone = 'CET';

mysql> SELECT FROM_UNIXTIME(1382825733) AS zero,  
              FROM_UNIXTIME(1382825733 + 3600) AS one, 
              FROM_UNIXTIME(1382825733 + 3600 + 3600) as two, 
              FROM_UNIXTIME(1382825733 + 3600 + 3600 + 3600) as three,
              FROM_UNIXTIME(1382825733 + 3600 + 3600 + 3600 + 3600) as four;
+---------------------+---------------------+---------------------+---------------------+---------------------+
| zero                | one                 | two                 | three               | four                |
+---------------------+---------------------+---------------------+---------------------+---------------------+
| 2013-10-27 00:15:33 | 2013-10-27 01:15:33 | 2013-10-27 02:15:33 | 2013-10-27 02:15:33 | 2013-10-27 03:15:33 |
+---------------------+---------------------+---------------------+---------------------+---------------------+
                                                         ^^ ... wait, what? .. ^^
1 row in set (0.00 sec)

如果在过渡时间内对不明确的值进行时区转换，则转换不是无损的。

对时间戳的操纵必须是UTC端到端的操作。使用FROM_UNIXTIME()或UNIX_TIMESTAMP()可以处理一方或另一侧的本机UTC值，但该值仍被转换到或从您的会话时区(或未设置会话时区时的服务器时区)转换到或从TIMESTAMP列中的某个值(该值实际上存储为UTC，并转换为/从您的会话时区)。

这就是您的服务器时钟应该始终使用UTC的原因之一。

票数 8

页面原文内容由Database Administration提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://dba.stackexchange.com/questions/161416

复制

相似问题

问为什么MySQL在使用FROM_UNIXTIME时计算一个错误的时间戳
EN

回答 1

Database Administration用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问为什么MySQL在使用FROM_UNIXTIME时计算一个错误的时间戳EN

回答 1

Database Administration用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问为什么MySQL在使用FROM_UNIXTIME时计算一个错误的时间戳
EN