Location>code7788 >text

MySQL Time Zone and serverTimezone

Popularity：639 ℃/2024-12-19 20:50:39

TL;DR

  1. Manually specify a non-offset time zone for MySQL to avoid theTIMESTAMP Type Daylight Saving Time Issues and Time Zone Conversion Performance Bottlenecks
  2. TIMESTAMP range: '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07'
  3. When connecting to a MySQL database, the serverTimezone parameter specifies the time zone of the database server, which needs to be set to the same time zone as the MySQL server.

MySQL Time Zone Settings Affect TIMESTAMP Type Data and Some Time Functions

MySQL session time zone settings affect theTIMESTAMP and time functions (NOW(), CURDATE(), CURTIME(), CURRENT_TIMESTAMP())

stockpileTIMESTAMP When you type data, MySQL converts the time to UTC time based on the time zone of the current session, and MySQL actually stores the UTC time. When retrieving, MySQL converts the stored UTC time to the time in the corresponding time zone of the session according to the time zone of the session. The time value stored in a field of type DATETIME is the original value and is not affected by the time zone.

MySQL uses the SYSTEM time zone (i.e., the operating system's time zone) by default, and every MySQL function call that requires a time zone calculation calls the system library to determine the current system time zone. This call may be protected by global mutexes, which can lead to contention, and it is recommended that you set the time zone explicitly

Query current time zone

# time_zone: MySQL uses SYSTEM's time zone
# system_time_zone: SYSTEM for CST time zone
show variables like "%time_zone%";
+------------------+--------+
| Variable_name | Value |
+------------------+--------+ | system_time_zone
| system_time_zone | CST |
| time_zone | system_time_zone | CST | SYSTEM |
+------------------+--------+

The effect of different session time zones on the time function

# Current time zone
# View the current global and session time zone values
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(); SELECT NOW(), CURDATE(), CURTIME(), CURRENT_TIMESTAMP()

Effect of different session time zones on TIMESTAMP type

# 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)

# Modify the time zone of the current session only
set time_zone = 'America/New_York';

SELECT * FROM events.

+----+----------------+---------------------+---------------------+
| id | event_name | event_timestamp | event_datetime |
+----+----------------+---------------------+---------------------+ | event_name | event_timestamp | event_datetime | event_datetime
| 1 | 10.24 15:45:00 | 2022-10-24 03:45:00 | 2022-10-24 15:45:00 | <- Daylight Saving Time, 12 hour difference
| 2 | 12.24 15:45:00 | 2022-12-24 02:45:00 | 2022-12-24 15:45:00 | <- 13 hours difference during weekdays
+----+----------------+---------------------+---------------------+
2 rows in set (0.00 sec)

The New York UTC time difference is usually UTC-5 (EST) and UTC-4 (EDT) for daylight saving time, so transferring the original session from Shanghai (UTC+8) to New York timeTIMESTAMP There is a difference of 13 or 12 (daylight saving time) hours, so to automatically convert to daylight saving time, it is best to specify the time zone using the time zone termAsia/Shanghai, avoid using offsets:'+08:00'

Impact of serverTimezone on TIMESTAMP Types for JDBC Connections to MySQL

We use URLs when connecting to MySQL:jdbc:mysql://192.168.1.2:3306/mydb?useSSL=false&serverTimezone=Asia/Shanghai

Here.serverTimezone parameter is used to specify the time zone to use when connecting to the MySQL database; it is not shown to specify the JVM default time zone.

MySQL Server-Side ProcessingTIMESTAMP : When writing, the time stamp is converted to UTC according to the session time zone, and when reading, the UTC is restored to the time of the session time zone, which ensures the consistency of the written and read data. When the database session time zone is the same as the JVM time zone, the JVM reads and writes the data in theTIMESTAMP consistent, and if they are not, there will be problems.serverTimezone It's to tell JDBC that the MySQL server got theTIMESTAMP If you know what time zone it's in, JDBC can preprocess the

MyBatis has a lot to offer when it comes to handlingTIMESTAMP There are some differences when it comes to the type of data, entities are mapped to theTimestamp maybeDate The preprocessing mentioned above is done when reading and writing, and theLocalDateTime failing agreement

When JDBC reads data of type TIMESTAMP

When JDBC executes a command, calling different ResultSet methods gives different results:

  • ResultSet's getString method: reads the time directly, i.e. returns it directly.Time converted by the database according to the session time zone
  • ResultSet's getTimestamp method: sets theTime converted by the database according to the session time zone Converted from the timezone set by serverTimezone, we getTime converted by the database according to the session time zone UTC time millisecond timestamp, and then converts this UTC millisecond timestamp to aTimestamp type (which itself does not contain time zone information), which is printed as the corresponding time zone time according to the JVM's time zone

getTimestamp isomerization (chemistry)Timestamp The source code is in:

Here. is the connection url to theserverTimezone

Assuming MySQL's default setting for the session time zone isAsia/ShanghaiThe MySQL JDBC driver reads this TIMESTAMP through the default session as 2022-10-24 15:45:00, whereas MySQL actually stores TIMESTAMP as UTC time: 2022-10-24 07:45:00. When the MySQL JDBC driver fetches this value through the default session, MySQL automatically provides the converted value based on the default time zone, and the driver provides the value based on the default time zone. Time: 2022-10-24 15:45:00, and the driver provides a converted value based on theserverTimezone time zone configured by MySQL to theCalendar object, by means of the() Get the corresponding UTC timestamp for creating theTimestamp boyfriend

JDBC writes to the TIMESTAMP type:

  • now() write, the database server will get the current time zone of the database.
  • Write as string: The MySQL server stores the number of milliseconds in UTC according to the session time zone.
  • Write via variable binding: pass in the Timestamp object, which JDBC encodes as a time string represented by serverTimezone, similarly:2022-06-22 03:29:29and then send it to the MySQL server

validate (a theory)

import ;

import .*;
import ;

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() {
        (("Asia/Shanghai"));

        try (Connection connection = (url, username, password); PreparedStatement ps = connection
                .prepareStatement("insert into events(id,event_name,event_timestamp,event_datetime) values (1,'now()',now(),now())");) {
            ();
        }catch (Exception e){
            ();
        }
        
        try (Connection connection = (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')");) {
            ();
        }catch (Exception e){
            ();
        }

        try (Connection connection = (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 (China Standard Time (CST))
            long timestamp = 1733539800000L;
            Timestamp ts1 = new Timestamp(timestamp);
            Timestamp ts2 = new Timestamp(timestamp);

            (1, ts1);
            (2, ts2);
            ();
            // ground serverTimezone commander-in-chief (military) Timestamp The preprocessing is UTC timing：2024-12-07 02:50:00
            // Equivalent to the implementation of the following 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){
            ();
        }
    }

    @Test
    void testGetTimestamp() {
        (("Asia/Shanghai"));

        try (Connection connection = (url, username, password); PreparedStatement ps = connection
                .prepareStatement("select * from events where id=3"); ResultSet rs = ();) {
            while (()) {
                // getTimestamp is 2024-12-07 10:50:00.0
                // ground serverTimezone，Determine that the time zone of the database is UTC，change into this locality Asia/Shanghai need +8，则The preprocessing is：2024-12-07 10:50:00.0
                ("getTimestamp is " + ("event_timestamp"));
                // getString is 2024-12-07 02:50:00
                ("getString is " + ("event_datetime"));
            }
        }catch (Exception e){
            ();
        }
    }
}

Experimental environment:
MySQL 8.0.40
mysql-connector-j 9.1.0
mybatis-spring-boot-starter 3.0.4

bibliography

7.1.15 MySQL Server Time Zone Support
13.2.2 The DATE, DATETIME, and TIMESTAMP Types
MySQL driver reads the time of day when the time zone is handled in a single article
Three ways to modify mysql time zone
Best Practices for Storing Time in MySQL

<Previous Next>