MySQL's time zone behavior is governed by three layers: the server-wide default set in my.cnf, the per-session value adjustable via SET time_zone, and the column type itself (TIMESTAMP versus DATETIME). Most timezone bugs come from confusing these layers. Knowing which one to change for a given symptom is the foundation of reliable operations.
The server default is configured with default-time-zone in the my.cnf file. The session value can be changed with SET time_zone = '+09:00' after connecting, and most ORM drivers do this automatically when establishing a connection. The column behavior, by contrast, is determined entirely by the type chosen in the schema definition and cannot be changed at runtime.
To check the current settings, run SELECT @@global.time_zone, @@session.time_zone;. The global value is the server-wide default, and the session value is what applies to your current connection. If either shows SYSTEM, MySQL is deferring to the operating system's time zone rather than a MySQL-specific setting, and you can see how that is being interpreted with SELECT @@system_time_zone;.
Whether IANA zone names such as Asia/Tokyo are usable depends on whether tzdata has been loaded into the time zone tables in the mysql database. If SELECT COUNT(*) FROM mysql.time_zone_name; returns 0, the data is not loaded and SET time_zone = 'Asia/Tokyo' fails with the ERROR 1298 described below. Offset notation like '+09:00' always works regardless, so the reliable diagnostic order is: check the current values first, then check whether tzdata is present.
TIMESTAMP stores values internally as UTC and converts them to the session time_zone on read. This means clients in different time zones see consistent local times for the same underlying instant. The valid range runs from 1970-01-01 00:00:01 UTC through 2038-01-19 03:14:07 UTC because the value is a 32-bit Unix epoch second count, and that ceiling is unchanged across the 8.0 series. What MySQL 8.0.28 widened on 64-bit platforms is the argument range accepted by UNIX_TIMESTAMP() and FROM_UNIXTIME(), which now reaches the year 3001, while the TIMESTAMP column type still stops at 2038.
DATETIME stores values as literal year-month-day-hour-minute-second strings without any time zone notion. The same value reads the same regardless of session settings, and the valid range stretches from 1000-01-01 to 9999-12-31. DATETIME looks simpler but becomes harder to use correctly in multi-zone systems because the meaning of the stored value depends on context that lives outside the database.
| Aspect | TIMESTAMP | DATETIME |
|---|---|---|
| What is stored | The instant converted internally to UTC | The value exactly as it was written, such as 2026-05-20 10:00:00 |
| Depends on time_zone when read | Yes. Converted to the session time_zone on read | No. The same value reads the same under any session setting |
| Valid range | 1970-01-01 00:00:01 UTC to 2038-01-19 03:14:07 UTC (from the 32-bit Unix epoch second representation) | 1000-01-01 to 9999-12-31 |
| DEFAULT CURRENT_TIMESTAMP | Available. The current time is converted to UTC using the session time_zone | Available from MySQL 5.6, but changing the server time_zone silently changes the meaning of values already written |
| What pins down the meaning | The type itself, because it always denotes an absolute instant | Context outside the database, recorded separately as which zone the value belongs to |
The recommended setup for a new project is to pin the server time_zone to UTC, use TIMESTAMP, and keep display conversion in the application layer.
After connecting, SET time_zone = 'Asia/Tokyo' makes subsequent TIMESTAMP reads, NOW(), and CURRENT_TIMESTAMP() use the specified zone. To use IANA zone names, the server must have time zone data loaded via mysql_tzinfo_to_sql. Many official MySQL Docker images do not include this data, so SET time_zone = 'Asia/Tokyo' fails with ERROR 1298 unless you load the data first.
If loading IANA data is impractical, you can use offset notation like SET time_zone = '+09:00' instead. This works for zones with stable year-round offsets but cannot represent DST. For systems serving DST-aware locales such as the U.S. or Australia, IANA names are essential and the upfront effort to load tzdata is worth it.
MySQL Connector/J accepts a serverTimezone or, in 8.0+, connectionTimeZone parameter in the JDBC URL. The recommended value in modern driver versions is connectionTimeZone=SERVER (which respects the server-side setting) or an explicit IANA zone name, and the default is LOCAL, meaning the JVM default time zone. Leave the parameter out and that default is what silently shifts Java's Instant or OffsetDateTime values on write. Note too that connectionTimeZone by itself does not change the server session variable time_zone, so add forceConnectionTimeZoneToSession=true when the session has to follow as well.
A common production accident is code that worked locally because the developer's JDK and MySQL agreed on JST, but produces 9-hour offsets on a UTC server in production. The cure is to specify connectionTimeZone explicitly in the JDBC URL and migrate all java.util.Date usage to java.time types like Instant or OffsetDateTime. Date carries hidden local-time-zone semantics that JDBC layers convert in surprising ways.
The pattern of TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP is widely used for created_at and updated_at columns. The current time is taken from the session's time_zone, then stored internally as UTC. If the server's time_zone is UTC, the stored values correspond exactly to the Unix epoch second. If it is JST, the same is true after conversion, but the act of changing the server's time_zone later does not retroactively reinterpret existing rows.
DATETIME columns can also default to CURRENT_TIMESTAMP starting in MySQL 5.6, but DATETIME does not store time zone, so changing the server time_zone changes the meaning of any value written before the change. This makes server time_zone a near-irreversible decision once data has accumulated, and any change should be planned with full awareness of the historical interpretation.
For greenfield projects, set the server time_zone to UTC, use TIMESTAMP columns, and represent values in application code as Instant or OffsetDateTime. The display layer is the only place that converts to a user's local time zone. This eliminates an entire class of bugs and makes multi-region deployments straightforward, because every replica interprets stored values identically.
Existing systems running on JST servers with DATETIME columns cannot switch overnight. The pragmatic plan is to use TIMESTAMP for new columns, set connectionTimeZone explicitly in JDBC URLs, log timestamps with explicit UTC offsets, and document each existing column's intended meaning. A multi-quarter migration that tightens the schema column by column is far safer than a flag-day cutover.
Was this article helpful?
Python 3.9 introduced zoneinfo as the standard library solution for IANA time zones, but the long-established pytz package remains in many codebases. This article compares the two, explains the naive vs aware boundary, and covers practical pitfalls including Windows tzdata, the fold attribute, and migration strategy.
The java.time API introduced in Java 8 offers five primary classes for representing dates and times. Knowing when to use Instant, OffsetDateTime, ZonedDateTime, LocalDateTime, or LocalDate is the key to avoiding time zone bugs. This article walks through each class with practical guidance for Spring Boot and JPA projects.
Building a timezone conversion API sounds simple but quickly hits IANA database management, DST gap handling, error semantics, and caching strategy. This article covers the design decisions that determine whether the endpoint stays reliable as policies and tzdata evolve.