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.
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 through 2038-01-19 because the original implementation used a 32-bit Unix epoch second representation. MySQL 8.0.28 expanded internal handling but kept the documented range for compatibility.
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.
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. Without it, the driver guesses based on heuristics, and Java's Instant or OffsetDateTime values may be silently shifted on write.
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.