# time_zone：MySQL 使用 SYSTEM 的時(shí)區(qū)
# system_time_zone：SYSTEM 為 CST 時(shí)區(qū)
show variables like "%time_zone%";
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | CST    |
| time_zone        | SYSTEM |
+------------------+--------+

# 當(dāng)前時(shí)區(qū) 
# 查看當(dāng)前的全球和會(huì)話時(shí)區(qū)值
SELECT @@GLOBAL.time_zone, @@SESSION.time_zone;
SELECT NOW(), CURDATE(), CURTIME(), CURRENT_TIMESTAMP();
set time_zone = 'America/New_York';
SELECT NOW(), CURDATE(), CURTIME(), CURRENT_TIMESTAMP();

# UTC +8
set time_zone = 'Asia/Shanghai';
CREATE TABLE events (
    id INT AUTO_INCREMENT PRIMARY KEY,
    event_name VARCHAR(255) NOT NULL,
    event_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    event_datetime DATETIME DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO events (event_name, event_timestamp, event_datetime) VALUES ('10.24 15:45:00', '2022-10-24 15:45:00', '2022-10-24 15:45:00');
INSERT INTO events (event_name, event_timestamp, event_datetime) VALUES ('12.24 15:45:00', '2022-12-24 15:45:00', '2022-12-24 15:45:00');

SELECT * FROM events;
+----+----------------+---------------------+---------------------+
| id | event_name     | event_timestamp     | event_datetime      |
+----+----------------+---------------------+---------------------+
|  1 | 10.24 15:45:00 | 2022-10-24 15:45:00 | 2022-10-24 15:45:00 |
|  2 | 12.24 15:45:00 | 2022-12-24 15:45:00 | 2022-12-24 15:45:00 |
+----+----------------+---------------------+---------------------+
2 rows in set (0.00 sec)

# 僅修改當(dāng)前會(huì)話的時(shí)區(qū)
set time_zone = 'America/New_York';

SELECT * FROM events;
+----+----------------+---------------------+---------------------+
| id | event_name     | event_timestamp     | event_datetime      |
+----+----------------+---------------------+---------------------+
|  1 | 10.24 15:45:00 | 2022-10-24 03:45:00 | 2022-10-24 15:45:00 |    <- 夏令時(shí)，相差 12 小時(shí)
|  2 | 12.24 15:45:00 | 2022-12-24 02:45:00 | 2022-12-24 15:45:00 |    <- 平時(shí)相差 13 小時(shí)
+----+----------------+---------------------+---------------------+
2 rows in set (0.00 sec)

import org.junit.jupiter.api.Test;
import java.sql.*;
import java.util.TimeZone;
public class JDBCTest {
    private static String url = "jdbc:mysql://host:3306/mydb?useSSL=false&serverTimezone=UTC";
    private static String username = "root";
    private static String password = "";
    @Test
    void testInsertTimestamp() {
        TimeZone.setDefault(TimeZone.getTimeZone("Asia/Shanghai"));
        try (Connection connection = DriverManager.getConnection(url, username, password); PreparedStatement ps = connection
                .prepareStatement("insert into events(id,event_name,event_timestamp,event_datetime) values (1,'now()',now(),now())");) {
            ps.execute();
        }catch (Exception e){
            e.printStackTrace();
        }
        try (Connection connection = DriverManager.getConnection(url, username, password); PreparedStatement ps = connection
                .prepareStatement("insert into events(id,event_name,event_timestamp,event_datetime) values (2,'2022-06-22 03:29:29','2022-06-22 03:29:29', '2022-06-22 03:29:29')");) {
            ps.execute();
        }catch (Exception e){
            e.printStackTrace();
        }
        try (Connection connection = DriverManager.getConnection(url, username, password); PreparedStatement ps = connection
                .prepareStatement("insert into events(id,event_name,event_timestamp,event_datetime) values (3,'1733539800000L',?,?)")) {
            // Sat Dec 07 2024 02:50:00 GMT+0000
            // Sat Dec 07 2024 10:50:00 GMT+0800 (中國標(biāo)準(zhǔn)時(shí)間)
            long timestamp = 1733539800000L;
            Timestamp ts1 = new Timestamp(timestamp);
            Timestamp ts2 = new Timestamp(timestamp);
            ps.setTimestamp(1, ts1);
            ps.setTimestamp(2, ts2);
            ps.execute();
            // 根據(jù) serverTimezone 將 Timestamp 預(yù)處理為 UTC 時(shí)間：2024-12-07 02:50:00
            // 相當(dāng)于執(zhí)行下列 SQL
            // insert into events(id,event_name,event_timestamp,event_datetime) values (3,'1733539800000L','2024-12-07 02:50:00','2024-12-07 02:50:00')
        }catch (Exception e){
            e.printStackTrace();
        }
    }
    @Test
    void testGetTimestamp() {
        TimeZone.setDefault(TimeZone.getTimeZone("Asia/Shanghai"));
        try (Connection connection = DriverManager.getConnection(url, username, password); PreparedStatement ps = connection
                .prepareStatement("select * from events where id=3"); ResultSet rs = ps.executeQuery();) {
            while (rs.next()) {
                // getTimestamp is 2024-12-07 10:50:00.0
                // 根據(jù) serverTimezone，認(rèn)定數(shù)據(jù)庫時(shí)區(qū)為 UTC，轉(zhuǎn)化為 本地 Asia/Shanghai 需要 +8，則預(yù)處理為：2024-12-07 10:50:00.0
                System.out.println("getTimestamp is " + rs.getTimestamp("event_timestamp"));
                // getString is 2024-12-07 02:50:00
                System.out.println("getString is " + rs.getString("event_datetime"));
            }
        }catch (Exception e){
            e.printStackTrace();
        }
    }
}