MySQL 8.0.22 Timezone Bug: Why Your Timestamps Are Off by 8 Hours and How to Fix It

Many Java developers have encountered timezone-related bugs, and even major tech companies have made headlines for 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 resolved. This article thoroughly explains the MySQL bug officially acknowledged in versions before 8.0.22, the JDBC driver parameters that control timezone behavior, and the best practices to avoid time discrepancies.

MySQL 8 Driver Timezone Parameters

Most Java developers have copied a JDBC connection string like:

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

But what do these parameters mean? Specifically, what does serverTimezone do? Why does the MySQL 8 driver force us to configure it? And where do the mysterious 8-hour, 13-hour, and daylight-saving-time crashes come from?

The Timezone Configuration Pitfall

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 — it's the value of 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 explicitly 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 common “tutorial” fix: add serverTimezone=UTC. The application runs, but 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 may be deprecated in the future. connectionTimeZone accepts three types of values:

A 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 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; changing the session timezone requires the next parameter.

forceConnectionTimeZoneToSession

A boolean. 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 a lifesaver or a source of accidents.

preserveInstants

Introduced in MySQL 8.0.23, this critical boolean 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. Default is true, introduced to fix behavioral differences when upgrading from 5.1 to 8.0.

DATETIME vs TIMESTAMP

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 remnant” in many upgraded projects.

Other Time-Related Parameters

Refer to the official documentation:

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, throws exception), CONVERT_TO_NULL (converts to null), ROUND (converts to 0001-01-01). Production usually uses CONVERT_TO_NULL. sendFractionalSeconds: whether to send fractional seconds (milliseconds). Default true; setting false silently truncates milliseconds, occasionally causing “time mismatch” ghosts.

Officially Acknowledged Bug

Bug #30962953 is a genuine official bug with Release Notes backing. MySQL 8.0.23 release notes state:

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 above; always explicitly specify serverTimezone=Asia/Shanghai; never rely on auto-detection.

Additionally, the zeroDateTimeBehavior enum rename causes startup failures. For example, upgrading from 5.x to 8.x yields:

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 uppercase CONVERT_TO_NULL, rejecting the old value. Fix: change directly to CONVERT_TO_NULL.

TIMESTAMP and 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 according to the session timezone; on read, it converts back. Hence 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, it's fine; once they diverge, trouble occurs.

Best Practices

After all this discussion, here's a practical configuration you can copy directly.

# 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 practices:

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; avoid CST (ambiguous), avoid UTC (8-hour pit), and don't rely on auto-detection (daylight-saving pit).

Ensure server OS, MySQL, and JDBC timezone configurations are mutually known and consistent; before going live, verify with SELECT NOW() against the application's current time.

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

Use CONVERT_TO_NULL for zero dates; don't let sendFractionalSeconds=false swallow milliseconds.

Check spring.jackson.time-zone for JSON interfaces; after ORM layer upgrades (e.g., Hibernate 6), regression-test time fields.

Store “instants” with TIMESTAMP + Instant / OffsetDateTime; store “literal values” (e.g., user's local appointment 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 acting as “translator”.

Many 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: the Y2038 problem and session timezone conversion.