Why Does MyBatis Insert Yesterday’s Date? Uncovering the Timezone Trap

Problem Statement

Colleague reported that a MyBatis insert stored a date that was one day earlier. The initial guess was a MyBatis bug, but the investigation quickly pointed to a timezone mismatch.

Hypothesis

Possible causes considered: the database column type datetime, a type mismatch in the MyBatis‑generated code, or an incorrect column definition such as using varchar for dates.

Investigation Steps

Check the database column type – it is datetime.

Check the entity field type – it is java.util.Date.

Reproduce the bug with a unit test.

@SpringBootTest
class DateTimeTests {
    @Resource
    private UserMapper userMapper;

    @Test
    public void testDate() {
        User user = new User();
        // other fields omitted
        user.setCreateDate(new Date());
        userMapper.insertSelective(user);
    }
}

Running the test shows the inserted timestamp is 14 hours behind the current time, confirming a timezone issue.

Timezone Diagnosis

Server Time

[root@xxx ~]# date
2021年 11月 25日 星期四 09:26:25 CST
[root@xxx ~]# date -R
Thu, 25 Nov 2021 09:33:34 +0800

The server uses CST (+08:00), which is correct for China.

Database Timezone Variables

show variables like '%time_zone%';
+----------------------------+-------+
| Variable                   | Value |
+----------------------------+-------+
| system_time_zone           | CST   |
| time_zone                  | SYSTEM|
+----------------------------+-------+

system_time_zone

is the global system timezone (CST), and time_zone defaults to SYSTEM.

Java Timezone

@Test
public void testDate() {
    System.out.println(System.getProperty("user.timezone"));
    // rest of test
}

Output: Asia/Shanghai, i.e., Java sees UTC+8.

Driver Behaviour

Debugging MySQL Connector/J shows that the driver reads time_zone as SYSTEM, then falls back to system_time_zone which is "CST". The driver converts "CST" via TimeZone.getTimeZone("CST"), which Java interprets as US Central Standard Time (UTC‑6).

public void configureTimezone() {
    String configuredTimeZoneOnServer = this.serverSession.getServerVariable("time_zone");
    if ("SYSTEM".equalsIgnoreCase(configuredTimeZoneOnServer)) {
        configuredTimeZoneOnServer = this.serverSession.getServerVariable("system_time_zone");
    }
    String canonicalTimezone = getPropertySet().getStringProperty(PropertyKey.serverTimezone).getValue();
    if (configuredTimeZoneOnServer != null) {
        if (canonicalTimezone == null || canonicalTimezone.isEmpty()) {
            canonicalTimezone = TimeUtil.getCanonicalTimezone(configuredTimeZoneOnServer, getExceptionInterceptor());
        }
    }
    if (canonicalTimezone != null && !canonicalTimezone.isEmpty()) {
        this.serverSession.setServerTimeZone(TimeZone.getTimeZone(canonicalTimezone));
    }
}

The mapping file TimeZoneMapping.properties maps "CST" to "CST", which the JDK resolves to the US zone, causing a 14‑hour offset when the driver formats the timestamp.

Solutions

Option 1 – Adjust MySQL Timezone

# mysql -uroot -p
set global time_zone = '+8:00';
set time_zone = '+8:00';
flush privileges;

After changing, show variables like '%time_zone%' reports time_zone = +08:00 and the unit test passes.

Option 2 – Specify Timezone in JDBC URL

url: jdbc:mysql://host:3306/db_name?useUnicode=true&characterEncoding=utf8&autoReconnect=true&serverTimezone=Asia/Shanghai

Adding serverTimezone=Asia/Shanghai forces the driver to use the correct zone, also fixing the bug.

Root Cause Summary

MySQL system_time_zone is set to "CST".

Connector/J reads time_zone as "SYSTEM", then uses the value of system_time_zone.

Java’s TimeZone.getTimeZone("CST") returns US Central time, not China Standard Time.

The driver’s SqlTimestampValueFactory formats the timestamp with this wrong zone, producing a 14‑hour shift.

Key Takeaways

Check server, database, and JVM timezone settings when timestamps appear incorrect.

MySQL variables system_time_zone and time_zone can be ambiguous; prefer explicit UTC offsets or region IDs.

When using MySQL Connector/J, always set serverTimezone in the JDBC URL to avoid implicit "CST" mapping.

Unit tests are an effective way to reproduce and isolate timezone bugs.