How can I find an exhaustive list of MySQL time zones?

Exhaustive List of MySQL Time Zones

Introduction

MySQL utilizes time zones to handle various date and time calculations, but it's not uncommon for users to encounter challenges in accessing a comprehensive list of MySQL time zones.

MySQL Time Zones Dependence on Host Operating System

The valid time_zone values in MySQL settings generally depend on the host operating system. However, locating an exhaustive list of supported time zones can be challenging.

Loading Time Zone Data into MySQL

By default, MySQL does not load time zone data unless manually loaded. To load it, execute the following command:

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

After loading the data, execute the following to view the time zone list:

USE mysql;
SELECT * FROM `time_zone_name`;

Unloading Time Zone Data

If desired, time zone data can be unloaded from MySQL by executing:

TRUNCATE `time_zone` ;
TRUNCATE `time_zone_leap_second` ;
TRUNCATE `time_zone_name` ;
TRUNCATE `time_zone_transition` ;
TRUNCATE `time_zone_transition_type` ;

Note on Dropping Tables

Never DROP these tables, as it can lead to severe issues.

Automatic Time Zone Updates

To ensure that time zones are updated regularly, you can enable root login without password prompting and set up a cron job to execute the following script once a day:

#!/bin/bash

if [ `find /usr/share/zoneinfo -mtime -1 | grep -v '\.tab' | wc -l` -gt 0 ]; then
    echo "Updating MySQL timezone info"
    mysql_tzinfo_to_sql /usr/share/zoneinfo 2>/dev/null | mysql -u root mysql
    echo "Done!\n"
fi

Configuring Time Zone for Calgary

To display time in Calgary local time, use the following time zone:

America/Edmonton

Example Query

The following query converts a timestamp to Calgary local time:

SELECT CONVERT_TZ('2023-03-08 15:00:00', 'GMT', 'America/Edmonton');

The above is the detailed content of How can I find an exhaustive list of MySQL time zones?. For more information, please follow other related articles on the PHP Chinese website!