Mysql
為什麼 MySQL 在使用 FROM_UNIXTIME 時會計算錯誤的時間戳
我已經調試了很多，最後設法在 mysql shell 中重現它。
我將時間戳儲存在具有欄位類型的 mysql 數據庫中
timestamp。我用來FROM_UNIXTIME()從我的腳本中更新它們，並UNIX_TIMESTAMP()在選擇它們時使用。當我沒有在連接中設置時區時，
SET time_zone =它可以正常工作。但是當我設置時區時，會發生以下情況：
UNIX_TIMESTAMP()仍然給出正確的結果。UPDATE table SET field = FROM_UNIXTIME(..)在數據庫中設置錯誤的值。- 設置錯誤的值，不對應伺服器時區和連接時區之間的偏移量。伺服器時區是亞洲/曼谷 (UTC + 7)，連接時區是歐洲/柏林 (UTC + 1)。但是，該值以 1 小時的差異儲存，而不是 6 小時。
- 再次讀取該值時，我得到了錯誤的值。
我知道它是 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 的原因之一。