數據庫時區那些事兒 - MySQL的時區處理

原文地址

當JVM時區和數據庫時區不一致的時候，會發生什么？這個問題也許你從來沒有注意過，但是當把Java程序容器化的時候，問題就浮現出來了，因為目前幾乎所有的Docker Image的時區都是UTC。本文探究了MySQL及其JDBC驅動對于時區的處理方式，并嘗試給出最佳實踐。

先給總結

DATE和TIME類型不支持時區轉換。
對于TIMESTAMP類型，MySQL會正確的根據connection時區（對于JDBC來說就是JVM時區）/服務端時區做轉換。
- JDBC程序不需要特別注意什么事情。只要保證JVM時區和用戶所在時區保持一致即可。
不要在服務器端做日期時間的字符串格式化（DATE_FORMAT()），因為返回的結果是服務端的時區，而不是connection的時區（對于JDBC來說就是JVM時區）。
CURRENT_TIMESTAMP(), CURRENT_TIME(), CURRENT_DATE()可以安全的使用，返回的結果會轉換成connection時區（對于JDBC來說就是JVM時區）。
CURRENT_TIME()有一個不知道是不是BUG的Bug #92453。

日期時間類型的時區

MySQL - The DATE, DATETIME, and TIMESTAMP Types：

MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the
current time zone for retrieval. (This does not occur for other types such as DATETIME.)
By default, the current time zone for each connection is the server's time. The time zone can be set on
a per-connection basis.
As long as the time zone setting remains constant, you get back the same value you store.
If you store a TIMESTAMP value, and then change the time zone and retrieve the value, the retrieved value
is different from the value you stored. This occurs because the same time zone was not used for conversion
in both directions.

簡而言之就是兩句話：

查詢TIMESTAMP類型所返回的值，會根據connection的時區（對于JDBC來說就是JVM時區）做轉換
在MySQL中只有TIMESTAMP類型會做時區轉換

為了驗證這個結論，我寫了一段程序來實驗，這個程序做了三件事情：

使用Asia/Shanghai時區構造一個日期java.util.Date：2018-09-14 10:00:00，然后插入到數據庫里（表：test，列：timestamp類型）
使用Asia/Shanghai時區把這個值再查出來，看看結果。
使用Asia/Shanghai時區，獲得這個字段的格式化字符串（使用DATE_FORMAT()函數）。
使用Europe/Paris時區重復第2-3步的動作

在運行程序之前，我們先用Docker啟動一個MySQL，它所在的MySQL的時區是UTC（除非特別設定，所有Docker Image時區都默認為UTC）：

docker run --name mysql-timezone-test \-e MYSQL_RANDOM_ROOT_PASSWORD=yes \-e MYSQL_DATABASE=testdb \-e MYSQL_USER=tz \-e MYSQL_PASSWORD=tz \-p 3306:3306 \-d mysql:8

下面是結果：

Insert data, Time Zone        : 中國標準時間
java.util.Date                : 2018-09-14 10:00:00
Insert into timestamp column  : 2018-09-14 10:00:00
--------------------
Retrieve data, Time Zone      : 中國標準時間
Retrieve java.util.Date       : 2018-09-14 10:00:00
Retrieve formatted string     : 2018-09-14 02:00:00
--------------------
Retrieve data, Time Zone      : 中歐時間
Retrieve java.util.Date       : 2018-09-14 04:00:00
Retrieve formatted string     : 2018-09-14 02:00:00

可以看到Retrieve java.util.Date返回的結果根據JVM時區做了轉換的。而Retrieve formatted string返回的結果則是UTC時間。

當前日期時間相關函數

MySQL與"當前日期時間"相關的函數有這么些，MySQL - Date and Time Functions：

The CURRENT_TIMESTAMP(), CURRENT_TIME(), CURRENT_DATE(), and FROM_UNIXTIME() functions return values
in the connection's current time zone, which is available as the value of the time_zone system variable.

而且根據文檔所講，它們返回的結果匹配當前連接所設定的時區。

為了驗證這個結論，同樣寫了一段程序，分別使用Asia/Shanghai和Europe/Paris來調用CURRENT_TIMESTAMP()、CURRENT_TIME()、CURRENT_DATE()。

下面是運行結果：

JVM Time Zone              : 中國標準時間
Test CURRENT_DATE()        : 2018-09-18
Test CURRENT_TIME()        : 10:55:41
Test CURRENT_TIMESTAMP()   : 2018-09-18 10:55:41.0
--------------------
JVM Time Zone              : 中歐時間
Test CURRENT_DATE()        : 2018-09-18
Test CURRENT_TIME()        : 03:56:02
Test CURRENT_TIMESTAMP()   : 2018-09-18 04:56:02.0

可以看到結果是基本符合文檔里的說明的，但是要注意，在Europe/Paris時區，CURRENT_TIME()和CURRENT_TIMESTAMP()的時間部分相差一小時。
看上去CURRENT_TIMESTAMP()返回的是UTC DST offset結果，而CURRENT_TIME()返回的是UTC offset結果，關于這個我登記了Bug #92453。
關于Europe/Paris的DST信息可以在這里找到Wiki - List of tz database time zones。

在MySQL客戶端操作時區

-- 查詢系統時區和session時區
SELECT @@global.time_zone, @@session.time_zone;-- 設置session時區
SET time_zone = 'Asia/Shanghai';

詳見：MySQL Server Time Zone Support

Docker啟動時設定時區

~~你可以在docker啟動的時候設定MySQL容器的時區，比如這樣-e TZ=Asia/Shanghai。~~

這個方法有問題，會出現時間錯亂，workaround是root用戶連接到MySQL，然后執行SET GLOBAL time_zone = 'Asia/Shanghai';。

這樣客戶端連接MySQL時，查詢的時間的時區都是Asia/Shanghai了。

參考資料

MySQL - The DATE, DATETIME, and TIMESTAMP Types
MySQL - Date and Time Functions
MySQL Server Time Zone Support
Wiki - List of tz database time zones
W3C- Working with timezone