MySQL 8 Timezone Bug Exposed: Why Timestamps Shift Before 8.0.23

Many Java developers have encountered time-related bugs, and major internet companies have made headlines for such mistakes. Recently a junior engineer at the author's company fell into the timestamp storage, retrieval, and conversion trap. With help from AI and senior engineers, the issue was quickly located. This article explains the MySQL bug officially acknowledged in versions before 8.0.22.

MySQL 8 Driver Timezone Parameters

Most Java developers have copied a connection string like:

jdbc:mysql://localhost:3306/xttblog?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai&zeroDateTimeBehavior=convertToNull

But what do these parameters mean? What does serverTimezone actually do? Why does the MySQL 8 driver force us to configure it? And where do those 8-hour, 13-hour, and daylight-saving-time crashes come from? This article clarifies everything.

The Timezone Configuration Pitfall

Why does the MySQL 8 driver force timezone configuration? When upgrading from MySQL 5.7 + Connector/J 5.1 to MySQL 8 + Connector/J 8.x, you likely encountered this error:

java.sql.SQLException: The server time zone value 'Öйú±ê׼ʱ¼ä' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specific time zone value if you want to utilize time zone support.

The garbled text 'Öйú±ê׼ʱ¼ä' is "中国标准时间" (China Standard Time) incorrectly decoded — the value returned by MySQL's system_time_zone on Chinese Windows. This error is practically a rite of passage for Chinese developers upgrading to 8.x, with countless questions on Alibaba Cloud's developer community.

The root cause: Connector/J 6.x/8.x rewrote timezone handling logic. The driver must know the session timezone when establishing a connection. Official documentation states that if timezone detection fails (mainly because the server uses a timezone abbreviation), you must set an explicit timezone or configure a different timezone on the server.

Hence the famous "tutorial-style" fix: copy serverTimezone=UTC. The program runs, but some business data may silently be off by 8 hours.

Timezone-Related Parameters

connectionTimeZone

serverTimeZone

and connectionTimeZone are the same category of parameter and the most core ones. serverTimeZone is the old name; from Connector/J 8.0.23 it becomes an alias for connectionTimeZone, and the official docs say it may be deprecated in the future. connectionTimeZone accepts three kinds of values:

A specific timezone, e.g., Asia/Shanghai, GMT+8 (written as GMT%2B8 in URLs). LOCAL — assumes the connection timezone matches the JVM default timezone (this is the default after 8.0.23). SERVER — lets the driver attempt to detect the timezone from MySQL session variables time_zone / system_time_zone.

Note: this parameter itself does not modify the MySQL session's time_zone variable; to change the session timezone you need the next parameter.

forceConnectionTimeZoneToSession

A boolean. When set to true, the driver forces the MySQL session timezone to the value specified by connectionTimeZone. This affects database functions like NOW() and CURRENT_TIMESTAMP, so it can be a lifesaver or a source of accidents.

preserveInstants

Added in MySQL 8.0.23, this is a critical parameter. It controls whether the driver performs "instant preservation" conversion for TIMESTAMP types: on write, converting the JVM timezone instant to the session timezone's literal value; on read, converting back. It is a boolean, default true, introduced in 8.0.23 to fix behavioral differences when upgrading from 5.1 to 8.0.

There is a trap: if you simultaneously set connectionTimeZone=LOCAL, forceConnectionTimeZoneToSession=false, and preserveInstants=false, then connectionTimeZone becomes completely ineffective because the official documentation explicitly states this combination is meaningless.

useLegacyDatetimeCode

A legacy parameter from the 5.1 era, default true, using the old date-handling code. false enables the new timezone conversion logic, roughly equivalent to connectionTimeZone=SERVER&preserveInstants=true in the new driver. MySQL 8 driver has removed this parameter; if present in the URL it is ignored — a harmless "archaeological relic" in many upgraded projects.

Other Time-Related Parameters

Reference:

https://mysql.net.cn/doc/connector-j/en/connector-j-connp-props-datetime-types-processing.html

. Two commonly used ones: zeroDateTimeBehavior: MySQL allows zero dates like 0000-00-00 00:00:00, but Java cannot represent them. Options: EXCEPTION (default, throw exception), CONVERT_TO_NULL (convert to null), ROUND (convert to 0001-01-01). Production usually uses CONVERT_TO_NULL. sendFractionalSeconds: whether to send fractional seconds (milliseconds), default true. Setting to false silently truncates milliseconds, occasionally causing mysterious "time mismatch" issues.

Officially Acknowledged Bug

Bug #30962953 is a genuine official bug backed by Release Notes. MySQL's 8.0.23 changelog states:

After upgrading from Connector/J 5.1 to 8.0, saving and then reading DATETIME and TIMESTAMP values sometimes yielded different results. This is because 5.1 did not preserve time instants by default, while 8.0.22 and earlier converted timestamps to the server session timezone before sending. This release introduces a new timezone conversion control mechanism; setting preserveInstants=false restores the 5.1 default behavior. (Bug #30962953, Bug #98695, Bug #30573281, Bug #95644)

In other words, 8.0.0 ~ 8.0.22 is a "danger zone" . If the server timezone is misconfigured (e.g., CST), the driver automatically uses the server timezone for conversion, triggering the 13-hour accident. Community practice (bugstack, 51CTO, etc.) summarizes: when upgrading from 5.1 to 8.x, always upgrade to 8.0.23 or later; always explicitly specify serverTimezone=Asia/Shanghai; never rely on auto-detection.

Additionally, there is a bug where the zeroDateTimeBehavior enum rename causes startup failure. For example, upgrading from 5.x to 8.x makes the application fail to start:

The connection property 'zeroDateTimeBehavior' acceptable values are: 'CONVERT_TO_NULL', 'EXCEPTION' or 'ROUND'. The value 'convertToNull' is not acceptable.

In 5.x everyone wrote zeroDateTimeBehavior=convertToNull; 8.x changed the enum values to uppercase CONVERT_TO_NULL, rejecting the old value. Fix: change directly to CONVERT_TO_NULL.

TIMESTAMP vs DATETIME

These two time types are the root of all confusion.

TIMESTAMP : represents an "instant" on the timeline. MySQL stores it internally in UTC. On write, it converts from the session timezone; on read, it converts back to the session timezone. Therefore it is sensitive to session timezone, and serverTimezone and forceConnectionTimeZoneToSession mainly serve it.

DATETIME : just a "wall-clock literal" without timezone semantics; it stores exactly what you give it. MariaDB's official documentation warns that DATETIME is often misused to store "instants". As long as client and server timezones match, everything works; once they diverge, problems appear.

Best Practices

Copy the following best-practice configuration for domestic business:

# Connection string (domestic business)
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/xttblog?\
useUnicode=true&characterEncoding=utf8&useSSL=true&\
serverTimezone=Asia/Shanghai&\
zeroDateTimeBehavior=CONVERT_TO_NULL&\
tinyInt1isBit=false

For more detail, follow these seven best-practice rules:

Use driver version 8.0.23+; avoid touching 5.1 legacy projects unless necessary, bypassing the official "danger zone".

Always explicitly set serverTimezone to an IANA name like Asia/Shanghai; do not use CST (ambiguous), do not casually write UTC (8-hour pit), do not rely on auto-detection (daylight-saving pit).

Server, MySQL, and JDBC timezone configurations must be mutually known and consistent; before going live, verify with SELECT NOW() against the program's current time.

Only consider forceConnectionTimeZoneToSession=true when you need database-side time ( NOW()) to match the Java side, and be aware it modifies the session timezone.

Zero dates: use CONVERT_TO_NULL; don't let sendFractionalSeconds=false swallow milliseconds.

JSON interface time must check spring.jackson.time-zone; after ORM layer upgrades (e.g., Hibernate 6), regression-test time fields.

Store "instants" with TIMESTAMP + Instant / OffsetDateTime; store "wall-clock literals" (e.g., user appointment local time) with DATETIME + LocalDateTime; never mix them.

Conclusion

Timezone issues can be big or small; fundamentally they are a three-party protocol problem: OS timezone, MySQL session timezone, JVM timezone, plus a JDBC driver doing "translation". Some veterans recommend using long to store timestamps — also a valid approach. Alternatively, use UTC + absolute time types (PostgreSQL timestamptz / MySQL DATETIME(6) or BIGINT milliseconds) for instants, and timezone-less types only for wall-clock times. Remember two things about MySQL's TIMESTAMP: Y2038 and session timezone conversion.