問題

今天在往數(shù)據(jù)庫查詢一條數(shù)據(jù)時突然發(fā)現(xiàn)插入的時間竟然比系統(tǒng)時間少了13個小時。。。

然后因為插入時間的問題延伸出之前寫的SQL中只要涉及到創(chuàng)建時間都有可能存在，也就是某月1號插入的數(shù)據(jù)，你在統(tǒng)計時，當(dāng)月并未統(tǒng)計，實際上是在上個月的統(tǒng)計數(shù)據(jù)中。。。。

這就尷尬了。。。

排查

1、首先查證測試環(huán)境數(shù)據(jù)庫時區(qū)：

SHOW VARIABLES LIKE ‘%time_zone%'

竟然設(shè)置的CST時區(qū)，之前不是啊，之后詢問主管才得知，當(dāng)前改造的老項目生產(chǎn)環(huán)境的數(shù)據(jù)庫使用的CST。。。

為了保證測試一致性，就修改了數(shù)據(jù)庫時區(qū)。。。

到此，直接找到時間相差13個小時的原因。。。。

解決

既然知道時間差，也知道時區(qū)了，那么問題就好解決了，

mysql中有一個函數(shù)CONVERT_TZ() 可以解決這個問題(這里為了更直觀，直接貼圖)

通過這條SQL查詢出來的時間

SELECT report_id,create_time FROM report_item WHERE report_id=2584

結(jié)果：

上圖中的時間就是CST時區(qū)時間，單實際上系統(tǒng)時間是：2021-05-21 10:05:12，相差足足13個小時

所以SQL修改為：

SELECT report_id,CONVERT_TZ(create_time, 'UTC','+13:00') as create_time FROM report_item WHERE report_id=2584

到這里，基本完美解決了時區(qū)轉(zhuǎn)換問題，后來度娘了一下這個問題，發(fā)現(xiàn)CST和GMT時間相差是13或14小時，至于是13小時還是14小時，取決于你傳遞給數(shù)據(jù)庫的時間

函數(shù)介紹：CONVERT_TZ(dt,from_tz,to_tz)

轉(zhuǎn)換datetime值dt，從 from_tz 由給定轉(zhuǎn)到 to_tz 時區(qū)給出的時區(qū)，并返回的結(jié)果值。

如果參數(shù)無效該函數(shù)返回NULL。

示例

yyyy-MM-dd格式

SELECT CONVERT_TZ('2021-5-21 15:30:00','UTC','+13:00') AS 北京時間;

這里注意一點，我的SQL時區(qū)是CST，但是我在這里用的UTC來作為標(biāo)準(zhǔn)時間，所以可以理解CST-UTC-GMT，所以最后我用了+13:00,這里也可能會有人問為什么不直接用CST來轉(zhuǎn)，這個嘛。。。

可能是我mysql版本問題？

我直接寫CST，竟然返回給我一個null。。。尷尬。。。

使用這樣的寫法可以解決現(xiàn)在的問題，但是最重要的一點就是從根源上去解決，不要去給數(shù)據(jù)庫設(shè)置默認(rèn)時區(qū)，如果一定要，那設(shè)置GMT或者UTC都可以，不用使用CST時區(qū)，因為這個時區(qū)存在很多很多的問題，這里就不意義贅述了。。。

大家可以去找度娘要關(guān)于CST時區(qū)的問題

總結(jié)

1、數(shù)據(jù)庫時區(qū)最好不要設(shè)置成CST，以免出現(xiàn)上面的錯誤

2、當(dāng)數(shù)據(jù)庫中的時間用的是時間類型時候，Java中可以用String，但是這種做法不是很國際化

3、在數(shù)據(jù)庫連接字符串中設(shè)置時區(qū)。

推薦這種方式，如下：

jdbc:mysql://xxxx:3306/table_name?serverTimezone=Asia/ShanghaiallowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8

以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。

最新評論