Handling Time Zones in MySQL Correctly

2020-01-03 · mysql, timezone, configuration

Time zone bugs are quiet. A report is off by an hour twice a year, a scheduled job fires at the wrong time after a server migration, or two servers disagree about when an order was placed. I've chased enough of these to say that nearly all of them come from MySQL using the OS time zone by default, mixed with TIMESTAMP and DATETIME columns that behave differently.

My rules on MySQL 5.7/8.0 and MariaDB 10.x: server in UTC, time zone tables loaded, a clear idea of the two column types, and conversion for display in the application. You need root on the box, SUPER (or SYSTEM_VARIABLES_ADMIN) in MySQL, and the tzdata package so /usr/share/zoneinfo exists.

What the server thinks now

SELECT @@global.time_zone, @@session.time_zone, @@system_time_zone, NOW(), UTC_TIMESTAMP();

A fresh install returns SYSTEM for the first two and something like CEST or KST for @@system_time_zone. SYSTEM means every TIMESTAMP value is converted to whatever the OS clock zone is, and that changes if someone runs timedatectl set-timezone on the host.

Load the time zone tables

Named zones such as Europe/Berlin only work after the mysql.time_zone* tables are populated. MySQL ships a converter for the system zoneinfo:

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql

Expect a few warnings about Warning: Unable to load '/usr/share/zoneinfo/iso3166.tab' as time zone. Skipping it.; those files are not zones and the warnings are harmless. Rerun this command whenever tzdata is updated, since daylight saving rules change. On MariaDB the tool has the same name.

Test it:

SELECT CONVERT_TZ('2020-01-01 12:00:00', 'UTC', 'Asia/Seoul');

NULL means the tables are still empty.

Pin the server to UTC

In /etc/my.cnf (CentOS) or /etc/mysql/mysql.conf.d/mysqld.cnf (Ubuntu):

[mysqld]
default-time-zone = '+00:00'

I use the numeric offset rather than 'UTC' so the server still starts even if the time zone tables are missing. Restart the service, or apply at runtime first:

SET GLOBAL time_zone = '+00:00';

Existing connections keep their session zone until they reconnect. With MySQL 8.0 you can use SET PERSIST time_zone = '+00:00'; instead of editing the file.

Also make the log timestamps consistent on MySQL 5.7 and 8.0:

log_timestamps = UTC

TIMESTAMP versus DATETIME

TIMESTAMP stores UTC internally. On write, the value is converted from the session time zone to UTC; on read, back to the session zone. Range is 1970 to 2038-01-19.
DATETIME stores the literal wall-clock value with no conversion and no zone. Range is 1000 to 9999.

So with two sessions in different zones inserting '2020-01-03 09:00:00', a TIMESTAMP column holds two different instants and a DATETIME column holds identical strings. Neither is wrong, but you must decide which you mean:

CREATE TABLE events (
  id INT PRIMARY KEY AUTO_INCREMENT,
  occurred_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,   -- an instant
  scheduled_local DATETIME NOT NULL,                           -- a wall-clock time
  scheduled_tz VARCHAR(64) NOT NULL DEFAULT 'UTC'              -- the zone that wall-clock belongs to
);

For "when did this happen" use TIMESTAMP (or DATETIME with the server pinned to UTC and the application always writing UTC). For "when is the meeting" store the local time plus its zone name, because a future local time cannot be converted to UTC reliably; the rules may change before then.

Convert for display, not for storage

I keep storage in UTC and let each connection, or the application, choose a display zone:

SET SESSION time_zone = 'Europe/Berlin';
SELECT id, occurred_at FROM events;   -- TIMESTAMP columns come back in Berlin time

or convert explicitly in the query when the column is DATETIME in UTC:

SELECT id, CONVERT_TZ(scheduled_local, 'UTC', 'America/New_York') AS scheduled_ny FROM events;

Connector/J users should set serverTimezone=UTC in the JDBC URL; without it the driver guesses from the server's system_time_zone abbreviation and fails on ambiguous ones.

Quick sanity check

SELECT @@global.time_zone, @@session.time_zone;
SELECT COUNT(*) FROM mysql.time_zone_name;
SELECT NOW(), UTC_TIMESTAMP(), UNIX_TIMESTAMP();

NOW() and UTC_TIMESTAMP() must be identical when the session zone is +00:00. Insert a row into events from a session set to Asia/Tokyo and read it back from a session set to +00:00; occurred_at should differ by nine hours while scheduled_local is unchanged.

Errors and oddities

ERROR 1298 (HY000): Unknown or incorrect time zone: 'Europe/Berlin': the time zone tables are not loaded, or the name is misspelled. Names are case-sensitive.
The server time zone value 'KST' is unrecognized or represents more than one time zone. from Connector/J: add serverTimezone=UTC to the connection string or set default-time-zone on the server.
ERROR 1292 (22007): Incorrect datetime value: '2020-03-29 02:30:00' for column 'occurred_at' during a bulk load: that local time does not exist in a zone that springs forward at 02:00. With the session in UTC there are no gaps or overlaps, which is another reason to load in UTC.
Replication: time_zone is replicated per statement in statement-based mode, but a master and slave with different default-time-zone settings and SYSTEM zones can still produce different TIMESTAMP values when DATETIME literals are inserted. Pin both to +00:00.

The database stores instants in UTC and doesn't know where the user is. After that, time zone bugs live in the application layer, where they are much easier to test.