MySQL Officially Acknowledges Timezone Bug: Versions Before 8.0.22 May Return Incorrect Times

Many Java developers have encountered timezone-related bugs, and even major internet companies have made such errors. Recently, a junior developer 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.

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 MySQL 8's driver force us to configure it? Where do those 8-hour, 13-hour, and daylight-saving-time crashes come from?

The Timezone Configuration Trap

When upgrading from MySQL 5.7 + Connector/J 5.1 to MySQL 8 + Connector/J 8.x, you likely saw 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 actually "中国标准时间" (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 domestic developers upgrading to 8.x drivers, with numerous identical questions on Alibaba Cloud's developer community.

The root cause: Connector/J 6.x/8.x rewrote timezone handling logic. The driver must explicitly 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.

This led to 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 state it "may be deprecated in the future." connectionTimeZone accepts three types of values:

Specific timezone, e.g., Asia/Shanghai, GMT+8 (URL-encoded as GMT%2B8) LOCAL — assumes the connection timezone matches the JVM default timezone (this is also the default value 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 variable. When set to true, the driver forces the MySQL session timezone to be replaced with the value specified by connectionTimeZone. This affects database functions like NOW() and CURRENT_TIMESTAMP, so it can be either 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, it converts the JVM timezone instant to the session timezone's literal value; on read, it converts 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, likely only remembered by veteran developers. It is Connector/J 5.1 specific. Default is true, using the old date-handling code; false enables the new timezone conversion logic, roughly equivalent to the new driver's connectionTimeZone=SERVER&preserveInstants=true. MySQL 8 driver has removed this parameter; configuring it is ignored — a harmless "archaeological relic" left in many old project URLs.

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 yields different results. This is because 5.1 did not preserve time instants by default, while 8.0.22 and earlier versions 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, you must upgrade to 8.0.23 or above; always explicitly specify serverTimezone=Asia/Shanghai, do not rely on auto-detection.

Additionally, there are bugs like zeroDateTimeBehavior enum rename causing startup failures. For example, upgrading the driver 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 enum values to all uppercase CONVERT_TO_NULL, and the old value is directly rejected. Fix: change 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 always stores it in UTC internally; on write it converts according to the session timezone, on read it restores according to the session timezone. So it is sensitive to session timezone, and serverTimezone and forceConnectionTimeZoneToSession mainly serve it.

DATETIME : Just a "wall-clock literal value" with no timezone semantics; it stores exactly what you give it. MariaDB's official documentation has a sharp warning: DATETIME is often misused to store "instants"; as long as client and server timezones match there's no problem, but once they diverge, things break.

Best Practices

Just copy the following best-practice configuration:

# 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 7 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 trap), do not rely on auto-detection (daylight-saving trap).

Server, MySQL, and JDBC timezone configurations must be mutually known and keep consistent expectations; 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 eat milliseconds.

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

Store "instants" with TIMESTAMP + Instant / OffsetDateTime; store "literal values" (e.g., user's local appointment time) with DATETIME + LocalDateTime; do not mix them.

Conclusion

Timezone issues can be big or small; fundamentally they may be 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: instants use UTC + absolute time types (PG timestamptz / MySQL DATETIME(6) or BIGINT milliseconds), wall-clock times use timezone-less types; meanwhile, remember two things about MySQL's TIMESTAMP: Y2038 and session timezone conversion.