MySQL Time Zone Configuration: Installation Standards, Session Variables, and Their Impact on Data

Many MySQL users encounter issues such as which time zone to set during installation, why Java applications show a 14‑hour offset from Beijing time, whether changing the server time zone affects existing data, and how data migration can introduce time‑zone errors.

Startup Parameters & System Variables

To specify the time zone at MySQL startup, use the default-time-zone parameter, for example:

-- Method 1: add to startup command
mysqld --default-time-zone='+08:00' &

-- Method 2: add to configuration file
[mysqld]
default-time-zone='+08:00'

After MySQL starts, the time_zone system variable controls the session time zone. It can be changed at runtime with the set command (but not in my.cnf ):

-- View current settings
mysql> show global variables like '%time%zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | CST    |
| time_zone        | +08:00 |
+------------------+--------+

-- Change global time zone (affects new sessions)
set global time_zone='+00:00';

-- Change current session time zone
set session time_zone='+00:00';

The accepted values follow the same format: 'SYSTEM' (use OS time), an offset such as '+08:00' or '-06:00' , or a named zone like 'Asia/Shanghai' (requires the time‑zone tables to be loaded via mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -S /tmp/mysqld.sock mysql ).

What the Time Zone Affects

Two main points:

NOW() and CURTIME() return values based on the session time zone. This also influences INSERT … VALUES(NOW()) and columns defined with DEFAULT CURRENT_TIMESTAMP .

TIMESTAMP columns store the session time zone. When retrieved, MySQL converts the stored UTC value to the current session zone. DATETIME columns store the literal value and are not affected.

mysql> set time_zone='+00:00';
select now(), CURTIME();
+---------------------+-----------+
| now()               | CURTIME() |
+---------------------+-----------+
| 2021-12-02 08:45:33 | 08:45:33  |
+---------------------+-----------+

mysql> set time_zone='+08:00';
select now(), CURTIME();
+---------------------+-----------+
| now()               | CURTIME() |
+---------------------+-----------+
| 2021-12-02 16:45:39 | 16:45:39 |
+---------------------+-----------+

mysql> set time_zone='+08:00';
create table t(ts timestamp, dt datetime);
insert into t values('2021-12-02 16:45:39','2021-12-02 16:45:39');
select * from t;
+---------------------+---------------------+
| ts                  | dt                  |
+---------------------+---------------------+
| 2021-12-02 16:45:39 | 2021-12-02 16:45:39 |
+---------------------+---------------------+

set time_zone='+00:00';
select * from t;
+---------------------+---------------------+
| ts                  | dt                  |
+---------------------+---------------------+
| 2021-12-02 08:45:39 | 2021-12-02 16:45:39 |
+---------------------+---------------------+

Conclusions & Practical Answers

1. What time zone should be set in MySQL installation? For Chinese deployments, add default-time-zone='+08:00' to my.cnf . Other regions should use the appropriate offset.

Do not use system because the OS time zone may be uncontrolled and adds a performance overhead.

2. Why does a Java application show a 14‑hour difference from Beijing time? The JDBC URL lacks the serverTimezone parameter, and MySQL defaults to the OS time zone (often reported as CST ). MySQL interprets CST as the US Central Standard Time (UTC‑6), causing the shift. Fix by explicitly setting MySQL to '+08:00' and adding serverTimezone=Asia/Shanghai (or the correct zone) to the JDBC URL.

3. Will changing MySQL’s time zone affect already stored data? No, it only changes how TIMESTAMP values are displayed; existing data remain unchanged.

4. Can data migration introduce time‑zone errors? Yes, especially when using mysqldump to export CSV. By default, mysqldump reads TIMESTAMP values in UTC, so the imported data may be off. Use the --skip-tz-utc option (or ensure the session time zone matches the source) to avoid the problem. When dumping SQL files, MySQL includes a SET TIME_ZONE='+00:00' statement; avoid --compact unless you also use --skip-tz-utc .

-- Example of dumping with UTC time zone
mysqldump -S /data/mysql/data/3306/mysqld.sock --single-transaction \
--master-data=2 -t -T /data/backup/test3 --fields-terminated-by=',' test t

-- Exported CSV shows UTC values
cat /data/backup/test3/t.txt
2021-12-02 08:45:39,2021-12-02 16:45:39

For archive queries such as --where="date(create_time) < date(now())" , the UTC conversion can also cause mismatches; include --skip-tz-utc to keep the original zone.