深入淺析mysql的timestamp存在的時區問題

本篇文章給大家帶來了關于mysql的相關知識，主要介紹了淺談mysql的timestamp存在的時區問題，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，下面一起來看一下，希望對大家有幫助。

推薦學習：mysql視頻教程

簡介

眾所周知，mysql中有兩個時間類型，timestamp與datetime，但當在網上搜索timestamp與datetime區別時，會發現網上有不少與時區有關的完全相反的結論，主要兩種：

timestamp沒有時區問題，而datetime有時區問題，原因是timestamp是以UTC格式存儲的，而datetime存儲類似于時間字符串的形式，示例博文：MySQL 中 datetime 和 timestamp 的區別與選擇timestamp也有時區問題，示例博文：mysql中timestamp時區的問題

兩種觀點讓人迷惑，那timestamp到底會不會有時區問題呢？

答：因為mysql數據庫未指定所在時區默認為美國中部時間
(UTC-06:00)，美國從“3月11日”至“11月7日”實行夏令時，美國中部時間改為 UTC-05:00，與 UTC+08:00 相差 13 小時,冬令時則相差14個小時。所以存儲的時候時間就已經有“誤差了”。
各位小伙伴使用timestamp類型的時候一定要注意指定時區，不管是在數據庫配置指定還是數據庫連接的參數設置，一定要指定時區。

serverTimezone=Asia/Shanghai  show variables like ‘%time_zone%'; set time_zone='+08:00';  select now();

基本概念

時區：
由于地域的限制，人們發明了時區的概念，用來適應人們在時間感受上的差異，比如中國的時區是東8區，表示為+8:00，或GMT+8，而日本的時區是東9區，表示為+9:00，或GMT+9，當中國是早上8點時，日本是早上9點，即東8區的8點與東9區的9點，這兩個時間是相等的。
另外時間還有如下兩個概念：

絕對時間：

如unix時間綴，是1970-01-01 00:00:00開始到現在的秒數，如：1582416000，這種表示是絕對時間，不受時區影響，也叫紀元時epoch。

本地時間：

相對于某一時區的時間，是本地時間，比如東8區的2020-02-23 08:00:00，是中國人的本地時間，而在此時，日本人的本地時間是2020-02-23 09:00:00，所以本地時間都是與某一時區相關的，脫離時區看本地時間，是沒有意義的，因為你并不知道這具體是指的什么時間點。

比如在Java中，Date對象是絕對時間，通過SimpleDateFormat格式化出來的yyyy-MM-dd HH:mm:ss形式的時間字符串，是本地時間，如果SimpleDateFormat沒有調用setTimeZone()顯示指定時區，那么默認用的是jvm運行在的操作系統上的時區，我們開發機上的時區基本都是GMT+8。

timestamp與datetime區別

如下，我創建了一張表，里面time_stamp是timestamp類型，date_time是datetime類型，create_timestamp、create_datetime是timestamp與datetime類型，但是它們可以由數據庫自動生成。

CREATE TABLE `time_test` (   `id` bigint unsigned,   `time_stamp` timestamp,   `date_time` datetime,   `create_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創建時間',   `create_datetime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創建時間',   PRIMARY KEY (`id`) )

1、首先將數據庫時區設置為+8:00，即中國的東8區

2、然后如下手動插入一個固定時間的數據，以及用now()函數插入當前時間

3、當插入完數據后，然后我們修改當前會話的時區為+9:00，即日本的東9區，然后再次查看數據

4、如上，定義為timestamp類型的列time_stamp、create_timestamp不管是手動插入的，還是now()函數插入的，東9區都比東8區的時間大1個小時，這是正確的，說明timestamp類型是時區相關的，然而定義為datetime類型的date_time、create_datetime字段，時間都沒有變化，這說明datetime類型是時區無關的。

結論：
timestamp在存儲上是包含時區的，而datetime是不包含時區，說明網上的第一種說法是對的。

再看個例子
我們將東8區的的2020-02-23 08:00:00轉換為unix時間綴（絕對時間），再插入數據庫試試？
如下，使用linux的date命令轉換時間串為unix時間綴：

$ "date" --date="2020-02-23 08:00:00 +08:00" +%s 1582416000

然后用mysql的()函數，將unix時間綴轉換為mysql時間類型來插入數據。

如上，查詢出來的時間，也是東9區的9點，時間也是正確的。

為什么網上又說timestamp類型存在時區問題？

我發現網上說timestamp有時區問題，都是應用端插入數據，然后到數據庫中去看，結果發現時間不一樣，因此我打算在Java中寫個Demo試一下，看能不能重現這個問題。

1、首先，下面是Java中Entity的定義，與上面的time_test表對應，注意，這里面時間屬性都是用Date類型定義的，如下：

2、然后，我寫了兩個接口/insert與/queryAll來插入與查詢數據，如下：

3、然后我把數據庫的時區設置為+09:00時區，即日本的東9區，如下：

4、然后調用/insert接口插入數據，注意我接口傳入的時間是東8區的8點，如下：

5、插入完后，去數據庫中查詢一把，如下：

可以看到，time_stamp字段時間是9點，且我已將數據庫時區設置為東9區，東9區的9點與東8區的8點，這兩個時間實際是相等的，因此時間數據沒錯。

6、然后我使用/queryAll接口將數據查詢出來，如下：

timeStamp屬性是1582416000000，這是毫秒級的時間綴，秒級則是1582416000，對應是東8區的2020-02-23 08:00:00，時間數據也沒錯！

7、然后我又將mysql時區修改回+8:00，并重啟我們的java應用，如下：

8、再查詢一下數據，如下：

timeStamp屬性還是1582416000000，時間沒有變化，這也是正確的。

那為什么網上會說timestamp存在時區問題？

經過一翻查看，我發現他們都提到了jdbc的serverTimezone，會不會是這個配置錯誤導致的呢？就先試試吧！
1、如圖，我把數據庫時區修改回+9:00時區，然后故意把jdbc的url上的serverTimezone配置為與數據庫不一致的GMT+8時區，然后重啟java應用，如下：

url: jdbc:mysql://localhost:3306/testdb?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf8

其中GMT%2B8就是GMT+8，因為在url上需要urlencode，所以就變成了GMT%2B8。

2、重新插入數據，注意插入的時間還是東8區的8點，如下：

3、然后，我再到數據庫中查詢一把，如下：

time_stamp中時間竟然是8點！要知道我們雖然插入的是東8區的8點，但當前會話可是東9區的，東8區的8點等于東9區的9點，所以正確顯示應該為9點才對，時間差了1小時！

4、然后，我又調用/queryAll接口查詢了一把，想看看mybatis查詢出來的時間數據對不對，如下：

可以看到timeStamp是1582416000000，秒級是1582416000，這個時間就是東8區的8點，東9區的9點啊！查詢出來的時間竟然是正確的，為什么？？？

serverTimezone的本質

為了找出問題所在，我調試了一下mysql的jdbc驅動代碼，終于弄明白了原因，主要可以看看如下這幾點：

1.mysql驅動創建連接后，會調用com.mysql.jdbc.ConnectionImpl#configureTimezone()來配置此連接的時區，如果配置了serverTimezone，則會使用serverTimezone配置的時區，沒配置時會去取數據庫中的time_zone變量，這就是為什么我們沒有配置serverTimezone變量時，結果也是正確的。

//若使用普通驅動，使用此方法配置mysql連接的時區 com.mysql.jdbc.ConnectionImpl#configureTimezone() //若使用cj驅動，使用此方法配置mysql連接的時區 com.mysql.cj.protocol.a.NativeProtocol#configureTimezone()

2.調用jdbc的setTimestamp()方法時，實際調用的是com.mysql.cj.jdbc.ClientPreparedStatement#setTimestamp()，這里面會根據serverTimezone指定的時區，將對應的Timestamp對象轉換為serverTimezone指定時區的本地時間字符串。

3.執行sql語句時，會執行com.mysql.cj.jdbc.ClientPreparedStatement#execute()，這里面sendPacket變量保存著真實會發送到mysql的sql語句。

注：看的是8.0.11版本mysql-connector-java驅動源碼，不同版本代碼會稍有差異，比如5.2.16版本驅動，jdbc url上需要同時配置這兩個配置：useTimezone=true&serverTimezone=GMT%2B8，且setTimestamp()對應的是com.mysql.jdbc.PreparedStatement#setTimestampInternal方法。

原理總結如下：
mysql驅動在發送sql前，會將jdbc中的Date對象參數，根據serverTimeZone配置的時區轉化為日期字符串后，再發送sql請求給mysql server，同樣在mysql server返回查詢結果后，結果中的日期值也是日期字符串，mysql驅動會根據serverTimeZone配置的時區，將日期字符串轉化為Date對象。

因此，當serverTimeZone與數據庫實際時區不一致時，會發生時區轉換錯誤，導致時間偏差，如下：
a、比如sql參數是一個Date對象，時間值是東8區的2020-02-23 08:00:00，注意它里面存儲的可不是2020-02-23 08:00:00這個字符串，它是Date對象(絕對時間)，只是我用文字表達出來是東8區的2020-02-23 08:00:00。
b、然后，由于serverTimeZone配置的是東8區，mysql驅動會將這個Date對象轉為2020-02-23 08:00:00，注意這時已經是字符串了，然后再將sql發送給mysql，注意這里的sql里面已經將Date參數替換為2020-02-23 08:00:00了，因為Date對象本身是無法走網絡的。
c、然后mysql數據庫接收到這個時間字符串2020-02-23 08:00:00后，由于數據庫時區配置是東9區，它會認為這個時間是東9區的，它會以東9區解析這個時間字符串，這時數據庫保存的時間是東9區的2020-02-23 08:00:00，也就是東8區的2020-02-23 07:00:00，保存的時間就偏差了1個小時。
d、查詢結果里時間為什么又對了呢，因為查詢結果返回了東9區的時間字符串，而java應用又將其理解為是東8區的時間，負負得正了！

將serverTimezone與mysql時區保持一致

so，那么如果我們將serverTimezone配置改正確，即與數據庫保持一致時，應該查詢到的時間就會是錯的，會少1個小時。

1、jdbc url中使用與數據庫一樣的東9區GMT+9，如下：

url: jdbc:mysql://localhost:3306/testdb?serverTimezone=GMT%2B9&useUnicode=true&characterEncoding=utf8

其中的GMT%2B9，即是GMT+9。

2、然后重啟Java應用，再查詢一把看看，如下：

返回的是毫秒級時間綴1582412400000，秒級就是1582412400，使用linux的date命令轉換為時間字符串形式：

$ "date" --date="@1582412400" +"%F %T %z" 2020-02-23 07:00:00 +0800

看到沒，它是東8區的7點，剛好差了1個小時。

3、所以，使用mysql的timestamp類型時，對于java應用來說，一定要保證jdbc url中的serverTimezone與數據庫中的時區配置是一致的。
另外一點是，當沒有配置serverTimezone時，mysql驅動會自動讀取mysql server中配置的時區，這里面也有坑！如下：

mysql驅動自動讀取數據庫時區的坑

3.1 mysql安裝好后，默認時區是SYSTEM，而SYSTEM指的是system_time_zone變量的時區，如下：

3.2 當mysql驅動讀到time_zone變量是SYSTEM時，會再去讀取system_time_zone變量，而system_time_zone對于國內來說，默認是CST，這是一個混亂的時區，是4個不同時區的縮寫，如下：

對于Linux或MySQL，會認為CST是中國標準時間(+8:00)，但Java卻認為CST是美國標準時間(-6:00)(注：可能和Java運行在Windows中有關)：
如下，linux中CST等于+0800，即中國時區：

$ "date" +"%F %T %Z %z" 2021-09-12 18:35:49 CST +0800

如下，java中CST等于-06:00，美國時區：

3.3 因此mysql驅動取到CST這個時區值時，它會以為這是-6:00時區，但MySQL卻理解為+8:00時區，因此MySQL時區一定不要配置為CST，而要配置為具體的時區，如+8:00，但如果MySQL時區為CST且不可修改的情況下，一定要配置jdbc的serverTimezone為清晰的時區(如：GMT+8)。

Entity中日期屬性是String呢？

1、我們將Entity對象中的時間屬性改為String（不推薦），如下：

2、然后也寫兩個接口，/insert2與/queryAll2，如下：

3、然后插入數據，注意這時我是直接將無時區的8點，作為參數給到sql的，如下：

4、然后再查詢一把，如下：

如上所示，time_stamp字段值是8點，但此時數據庫時區是東9區，所以這是東9區的8點。
5、然后我將數據庫與jdbc中serverTimezone都改為東8區呢，改完后重啟Java應用，如下：

url: jdbc:mysql://localhost:3306/testdb?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf8

6、再次插入數據，參數還是無時區的8點，如下：

7、再查詢一把，如下：

如上所示，time_stamp字段值是8點，但現在數據庫時間是東8區，所以這是東8區的8點。
8、然后我再將jdbc url上的serverTimezone調整為東9區，然后重啟Java應用，如下：

url: jdbc:mysql://localhost:3306/testdb?serverTimezone=GMT%2B9&useUnicode=true&characterEncoding=utf8

現在serverTimezone與數據庫中不一致，數據庫是東8區，serverTimezone是東9區。
9、我們再次插入無時區的8點，如下：

10、然后再查詢一把，如下：

time_stamp字段值還是8點，數據庫是東8區，所以這是東8區的8點，但我們serverTimezone與數據庫的時區不一致啊，沒看到時間有偏差，為什么？

解釋一下
前面說過了，對于jdbc中的Date對象，在發送給mysql前，會先根據serverTimezone轉換為相應時區的時間字符串，但現在Entity中時間屬性是String類型，mysql驅動不會進行轉換，所以不管serverTimezone怎么配置，對String類型的時間串都沒影響。

這樣的話，似乎java中日期類型用時間字符串來存還好些，不容易出錯，但請再認真考慮一下，調用方傳了一個無時區的8點，數據庫自作主張，就將其認為是東9區的8點，但如果這個時間字符串實際是東8區的8點呢？這時如果保存到數據庫中為東9區的8點，那數據就存錯了！

那如果目前api接口就傳的無時區的時間串，Entity中就定義的String，怎么解決呢？
1、詢問接口定義人員，這個接口的時間串指的是哪個時區的，比如是東8區的2020-02-23 08:00:00。
2、然后接口接收到時間后，要以東8區將時間字符串轉換為Date對象，如下：

SimpleDateFormat sdf = new SimpleDateFormat('yyyy-MM-dd HH:mm:ss'); sdf.setTimeZone(TimeZone.getTimeZone("GMT+8")); Date date = sdf.parse("2020-02-23 08:00:00");

3、然后如果Entity中時間屬性定義的是String，那么我們要再將Date對象以數據庫的時區格式化為對應的時間字符串，比如數據庫時區是東9區，那么格式化后就是2020-02-23 09:00:00，如下：

SimpleDateFormat sdf = new SimpleDateFormat('yyyy-MM-dd HH:mm:ss'); sdf.setTimeZone(TimeZone.getTimeZone("GMT+9")); String dateStr = sdf.format(date); entity.setTimeStamp(dateStr);

4、然后將Entity保存到mysql中的，就也會是東9區的2020-02-23 09:00:00，結果正確。

所以，使用String類型來存儲時間數據，要想將時間值保存正確，超級麻煩，不建議在實際開發中這種使用。

最佳實踐

1、大多數團隊會規定api中傳遞時間要用unix時間綴，因為如果你傳一個2020-02-23 08:00:00時間值，它到底是哪個時區的8點呢？對于unix時間綴，就不會有此問題，因為它是絕對時間。而如果某些特殊原因，一定要使用時間字符串，最好使用ISO8601規范那種帶時區的時間串，比如：2020-02-23T08:00:00+08:00。

2、Mybatis中Entity定義要與數據庫定義一致，數據庫中是timestamp，那么Entity中要定義為Date對象，因為mysql驅動在執行sql時，會自動根據serverTimezone配置幫你轉換為數據庫時區的時間串，如果你自己來轉換，你極有可能因為忘記調用setTimeZone()方法，而使用當前java應用所在機器的默認時區，一旦java應用所在機器的時區與數據庫的時區不一致，就會出現時區問題。

3、jdbc的serverTimezone參數，要配置正確，當不配置時，mysql驅動會自動讀取mysql server的時區，此時一定要將mysql server的時區指定為清晰的時區(如：+08:00)，切勿使用CST。

4、如果數據庫時區修改后，jdbc的serverTimezone也要跟著修改，并重啟Java應用，就算沒有配置serverTimezone，也需要重啟，因為mysql驅動初始化連接時，會將當前數據庫時區緩存到一個java變量中，不重啟Java應用它不會變。

數據庫中用timestamp還是int來存儲時間？

如果用int型時間綴存儲，不管數據庫時區是啥，都不影響，因為存儲的是絕對時間，看起來完美解決了時區問題。
但從某些角度看，這種方案只是把時區問題從數據庫端推到應用端去了，時區問題將出現在將時間字符串轉換為時間綴的過程中，比如某程序員從api接口中拿到時間字符串后，沒考慮時區，直接轉為unix時間綴，就可能出現時區問題。
因此，對于不帶時區的時間串解析，一定要問清楚這是哪個時區的時間，并在代碼中顯式指定！

另外，用int存儲時間還有如下3個不好的點：

開發人員看到這個字段后，無法一目了然的了解到這個時間綴大概是個什么時間，需要去轉換一下，會很繁瑣。像update_time這樣的字段，數據庫提供了DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP的機制，這樣在更新任何字段時，update_time會自動更新，而如果使用int存儲，就需要程序員每次更新表時，重新set這個字段，容易遺忘。由于int只有4個字節，用它來存儲時間，會在2038年后溢出，而對于timestamp來說，MySQL將其底層存儲統一修改為8個字節，相對來說還是比較容易的。

當然，也并不是建議不用int，這是見仁見智的，不管用timestamp還是int，都沒有致命性問題的。

總結

timestamp本身是沒有時區問題的，時區問題是由于serverTimezone配置錯誤、mysql使用CST這種混亂時區或Entity中將日期定義String類型導致的。

推薦學習：mysql視頻教程