Mysql

為什麼 MySQL 在使用 FROM_UNIXTIME 時會計算錯誤的時間戳

  • January 18, 2017

我已經調試了很多,最後設法在 mysql shell 中重現它。

我將時間戳儲存在具有欄位類型的 mysql 數據庫中timestamp。我用來FROM_UNIXTIME()從我的腳本中更新它們,並UNIX_TIMESTAMP()在選擇它們時使用。

當我沒有在連接中設置時區時,SET time_zone =它可以正常工作。但是當我設置時區時,會發生以下情況:

  1. UNIX_TIMESTAMP()仍然給出正確的結果。
  2. UPDATE table SET field = FROM_UNIXTIME(..)在數據庫中設置錯誤的值。
  3. 設置錯誤的值,不對應伺服器時區和連接時區之間的偏移量。伺服器時區是亞洲/曼谷 (UTC + 7),連接時區是歐洲/柏林 (UTC + 1)。但是,該值以 1 小時的差異儲存,而不是 6 小時。
  4. 再次讀取該值時,我得到了錯誤的值。

我知道它是 FROM_UNIXTIME() 不起作用,因為當我打開另一個沒有連接特定時區的連接時,我看到了錯誤的值,直到我再次更新它。

相差 1 小時的事實讓我認為這可能是夏令時問題。因為柏林有夏令時,而曼谷沒有(據我所知)。

這是我複制此行為的 mysql shell 的未修改日誌。

伺服器時區是亞洲/曼谷 (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 行為正確——您的測試無效。

如果您使用 DST 在時區往返,則如果您遇到轉換,您將不會進行無損轉換。有問題的時間戳發生在“CET”和“Europe/Berlin”的 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)

……一小時兩秒後。

或者,翻轉它。

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()或使用本機 UTC 值,但該值仍會在另一側與您的會話時區(或伺服器時區,如果未設置會話時區)相互轉換 - 在UNIX_TIMESTAMP()作為列中的值的方式TIMESTAMP(實際上儲存為 UTC,並轉換為/從您的會話時區轉換)。

這就是為什麼您的伺服器時鐘應始終使用 UTC 的原因之一。

引用自:https://dba.stackexchange.com/questions/161416