How Do I Effectively Configure Time Zones in MySQL?

How to Configure Time Zone in MySQL: In-Depth Guide

MySQL offers flexibility in managing time zones through various methods. Here's a comprehensive guide to assist you in setting the time zone effectively.

Default Time Zone in my.cnf File

In the MySQL configuration file ("my.cnf"), the default time zone can be specified within the [mysqld] section using the following directive:

default-time-zone='+00:00'

This line sets the default time zone to UTC (Coordinated Universal Time).

Global Time Zone Variable

The @@global.time_zone variable stores the global time zone setting. To check its current value:

SELECT @@global.time_zone;

To set the global time zone:

SET GLOBAL time_zone = '+8:00';
SET GLOBAL time_zone = 'Europe/Helsinki';

Session Time Zone Variable

The @@session.time_zone variable controls the time zone for the current connection. To view its value:

SELECT @@session.time_zone;

To set the session time zone:

SET time_zone = 'Europe/Helsinki';
SET time_zone = "+00:00";

Both @@session.time_zone and @@global.time_zone can return SYSTEM, indicating that the time zone is inherited from my.cnf.

Additional Notes

To enable named time zones (e.g., 'Europe/Helsinki'), the MySQL timezone tables must be properly populated.

The TIMEDIFF() function can be used to obtain the current time zone offset.

UNIX_TIMESTAMP() and UNIX_TIMESTAMP(NOW()) provide the current UNIX timestamp.

For converting timestamps to UNIX timestamps, use SELECT UNIX_TIMESTAMP(timestamp) FROM table_name.

To retrieve a UTC datetime column as a UNIX timestamp: SELECT UNIX_TIMESTAMP(CONVERT_TZ(utc_datetime, ' 00:00', @@session.time_zone)) FROM table_name.

Changing the time zone does not modify stored datetime or timestamp values, but affects how they are displayed, as they are internally stored as UTC timestamps.

The above is the detailed content of How Do I Effectively Configure Time Zones in MySQL?. For more information, please follow other related articles on the PHP Chinese website!