【資料庫】時區及JDBC的時區設定

2023-05-24 12:00:56

JDBC連線時有個TimeZone設定,這玩意到底有用嗎?我是使用Postgresql和Mysql兩個資料庫驗證的。結果如下:

資料庫 部署方式 版本 JDBC連線TimeZone引數 JDBC連線serverTimezone引數 總結
Mysql docker 8.0 沒用 有用,會使用使用者端時區與設定的引數時區比較,並返回使用者端時區對應的時間,詳見範例1 Mysql使用JDBC連線時可以設定serverTimezone引數告訴使用者端伺服器的時區,因為Mysql的TimeStamp型別是沒有時區概念的,且沒有timestamp with time zone資料型別,只能通過連線時設定資料庫server的時區,並通過兩時區的對比,返回正確的使用者端時區的時間
Postgresql docker 15.3 沒用 沒用 Postgresql使用JDBC連線時,時區引數均無效,但Postgresql有timestamp with time zone資料型別,使用該資料型別時不管資料庫是什麼時區設定,也不管使用者端是什麼時區設定,只要你的使用者端時區不變,你存取得到的都是同一個時間,該時間的時區取決於使用者端的時區。資料庫中儲存的是對應的UTC時區時間,比如:2023-05-24 00:51:24.578703 +00:00,詳見範例2

 

 

 

 

 

範例1:

使用Mysql資料庫,建表DDL如下:

create table test
(
    a int       null,
    b timestamp null
);

資料庫中儲存的資料:

 使用JDBC連線,不設定serverTimezone引數,使用者端時區為東八區,執行以下SQL並用Java Date型別接收輸出:

SELECT b FROM test WHERE a = 1;
SELECT now();

輸出結果,注意Java的Date型別本身是沒有時區屬性的,這裡之所以輸出了CST可以自行研究Date的toString方法:

Tue May 23 15:13:46 CST 2023
Wed May 24 01:11:17 CST 2023

使用JDBC連線,設定serverTimezone引數為serverTimezone=UTC,使用者端時區為東八區,執行上面的SQL並用Java Date型別接收輸出:

Tue May 23 23:13:46 CST 2023
Wed May 24 09:12:52 CST 2023

總結:

當不使用serverTimezone設定時,JDBC連線中伺服器時區被認為與使用者端時區相同,因此資料庫中儲存的時間返回時被認為是東八區時間,無需變動,但SELECT now();返回的是Mysql伺服器時間,即UTC時間。設定serverTimezone引數後,JDBC連線中伺服器時區為UTC,使用者端時區為東八區,查詢資料庫中時間被認為是UTC時間,返回給使用者端時進行+8小時操作,同時SELECT now();返回值經過時區轉換,正確的返回了東八區時間。

範例2:

使用Postgresql資料庫,建表DDL如下:

create table test
(
    a integer,
    b timestamp with time zone
);

使用者端時區為東八區,分別執行以下SQL:

INSERT INTO test VALUES (2, now());
SELECT b FROM test WHERE a = 2;
SELECT now();

查詢結果如下:

Wed May 24 09:39:39 CST 2023
Wed May 24 09:39:39 CST 2023

資料庫中儲存結果:

修改使用者端時區為東九區,重新執行以上SQL:

Wed May 24 10:42:17 KST 2023
Wed May 24 10:42:17 KST 2023

資料庫中儲存結果:

總結:

使用Postgresql的timestamp with time zone資料型別時,不管資料庫是什麼時區設定,也不管使用者端是什麼時區設定,只要你的使用者端時區不變,你存取得到的都是同一個時間,該時間的時區取決於使用者端的時區。資料庫中儲存的是轉換後的UTC時間。

 

綜上:

使用Mysql時,JDBC連線中應正確設定serverTimezone引數;使用Postgresql時,使用timestamp with time zone資料型別。來保證獲取時間時時區的正確性。

另外,建議能不用資料庫的now()就別用了,資料庫時區會搞得你頭疼;更近一步,能別用時間(Date)就別用了,時間戳沒有時區的概念,當前端需要顯示的時候再通過DateFormat或Json轉換設定時區即可,可以省去很多需要考慮時區的工作。

 

以上是我自己的實驗結果,如果你有不同的結論,歡迎一起探討。