How can I get a comprehensive list of MySQL time zones?

MySQL Time Zones: An Exhaustive Guide

Question: Is there a comprehensive list of available MySQL time zones?

Answer: Yes, but it varies depending on the host operating system. To access the list, you need to load time zone data into MySQL.

How to Load Time Zone Data:

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

Note: This command is for Unix systems; the equivalent for Windows is not known.

Listing Time Zones:

Once the data is loaded, you can view the list of time zones:

USE mysql;
SELECT * FROM `time_zone_name`;

Calgary Local Time:

To display the time in Calgary's local time, use the following syntax:

SELECT CONVERT_TZ(CURRENT_TIMESTAMP, 'GMT', 'America/Edmonton');

Additional Notes:

• The time zone data takes up approximately 5 MB in MySQL.
• To unload the data, execute the following commands and restart MySQL:

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

• Caution: Do not drop these tables, as it will cause issues.

Automated Time Zone Updates:

You can set up an automated system to update MySQL time zones when the system time zone changes.

For MySQL >= 5.6.6:

mysql_config_editor set --login-path=client --host=localhost --user=root --password

For MySQL < 5.6.6:

Create a ~/.my.cnf file with the following contents:

[client]
user=root
password=yourMysqlRootPW

Then, add the following script to crontab to run daily:

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
Remember, this is mostly untested. If you encounter any issues, report them to update the answer accordingly.
The above is the detailed content of How can I get a comprehensive list of MySQL time zones?. For more information, please follow other related articles on the PHP Chinese website!