Why MySQL timestamps differ by 14 hours and how to fix the timezone bug

Problem

The incident table defines created_time with DEFAULT CURRENT_TIMESTAMP and a manually set recovery_time. created_time is stored correctly as Beijing time (UTC+08), but recovery_time is offset by 14 hours.

Investigation

JVM timezone

System.setProperty("user.timezone", "UTC");

Database timezone

show variables like '%time_zone%';
-- system_time_zone: CST
-- time_zone: SYSTEM

The CST value is ambiguous; MySQL interprets it as US Central (UTC‑06). This explains the 14‑hour gap (UTC‑06 + MySQL +08 = +14).

MyBatis handling

MyBatis stores timestamps using Instant via InstantTypeHandler. The Instant parameter is already in UTC, and Timestamp.from(parameter) creates a UTC Timestamp.

@UsesJava8
public class InstantTypeHandler extends BaseTypeHandler<Instant> {
    @Override
    public void setNonNullParameter(PreparedStatement ps, int i,
                                    Instant parameter, JdbcType jdbcType) throws SQLException {
        ps.setTimestamp(i, Timestamp.from(parameter));
    }
    // other methods omitted
}

MySQL Connector/J conversion

The driver adjusts the timestamp based on the session’s default timezone, obtained via session.getServerSession().getDefaultTimeZone(). In this environment it resolves to US Central (UTC‑06). The driver therefore treats the incoming UTC timestamp as if it were UTC‑06, adds a 6‑hour offset, and MySQL further adds its own +8‑hour offset, resulting in a total 14‑hour shift.

@Override
public void setTimestamp(int parameterIndex, Timestamp x, Calendar targetCalendar, int fractionalLength) {
    // truncate/precision handling omitted for brevity
    this.tsdf = TimeUtil.getSimpleDateFormat(this.tsdf,
        "''yyyy-MM-dd HH:mm:ss",
        targetCalendar != null ? targetCalendar : this.session.getServerSession().getDefaultTimeZone());
    StringBuffer buf = new StringBuffer();
    buf.append(this.tsdf.format(x));
    if (this.session.getServerSession().getCapabilities().serverSupportsFracSecs()) {
        buf.append('.');
        buf.append(TimeUtil.formatNanos(x.getNanos(), 6));
    }
    buf.append('\'');
    setValue(parameterIndex, buf.toString(), MysqlType.TIMESTAMP);
}

Solution

Set the MySQL server timezone to Beijing (UTC+08) and restart the service:

SET GLOBAL time_zone = '+08:00';
-- or for the current session: SET time_zone = '+08:00';

Alternatively, specify the timezone in the JDBC URL so the driver uses the correct offset without changing the server configuration:

jdbc:mysql://localhost:3306/table_name?useTimezone=true&serverTimezone=GMT%2B8

After applying either the server‑side or connection‑side timezone setting, recovery_time aligns with the expected Beijing time, eliminating the 14‑hour discrepancy.