淺談mysql的timestamp存在的時區問題

2022-07-14 14:05:58

簡介
基本概念
timestamp與datetime區別
為什麼網上又說timestamp型別存在時區問題？
那為什麼網上會說timestamp存在時區問題？
serverTimezone的本質
將serverTimezone與mysql時區保持一致
Entity中日期屬性是String呢？
最佳實踐
資料庫中用timestamp還是int來儲存時間？
總結

簡介

眾所周知，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，都沒有致命性問題的。