How to Set the MySQL Time Zone: Configuration File, Global, and Session Variables?

How to Set the Time Zone of MySQL

When working with MySQL, ensuring the correct time zone is configured is crucial to accurate timestamp handling and data manipulation. MySQL allows you to set the time zone in three locations:

1. my.cnf Configuration File

In the [mysqld] section of the my.cnf file, specify the default-time-zone parameter, such as:

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

2. Global Time Zone Variable (@@global.time_zone)

Use the SELECT @@global.time_zone statement to view the current setting. To change the global time zone, use:

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

3. Session Time Zone Variable (@@session.time_zone)

Check the session time zone with SELECT @@session.time_zone. Set it temporarily with:

SET @@session.time_zone = '+00:00';
SET time_zone = 'Europe/Helsinki';

For named time zones (e.g., 'Europe/Helsinki') to work, the timezone information tables must be properly populated.

Additional MySQL Time Zone Functions:

• Get the current time zone offset as a time value: SELECT TIMEDIFF(NOW(), UTC_TIMESTAMP);
• Get the current UNIX timestamp: SELECT UNIX_TIMESTAMP();
• Get a timestamp column as a UNIX timestamp: SELECT UNIX_TIMESTAMP(timestamp) FROM table_name`
• Get 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 affect existing timestamps or datetime values, but it will display them differently based on the new time zone setting.

The above is the detailed content of How to Set the MySQL Time Zone: Configuration File, Global, and Session Variables?. For more information, please follow other related articles on the PHP Chinese website!