How Can I Effectively Use Time Zones in MySQL?

MySQL Time Zones

Wondering about time zone support in MySQL? Here's a comprehensive guide to help you understand and use MySQL time zones effectively:

Exhaustive List of MySQL Time Zones

By default, MySQL does not include time zone information. To load time zone data into MySQL, execute the following command:

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

Once loaded, you can view the list of available time zones by running:

USE mysql;
SELECT * FROM `time_zone_name`;

Using Time Zones

To specify a time zone for a timestamp value, use the CONVERT_TZ() function:

CONVERT_TZ('2012-06-07 12:00:00', 'GMT', 'America/New_York')

This will convert the timestamp to Calgary local time.

Loading Time Zone Data Automatically

To automatically update MySQL time zones when the system time zone changes, configure MySQL to allow root login without a password:

MySQL >= 5.6.6

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

MySQL < 5.6.6

Create a ~/.my.cnf file:

user=root
password=yourMysqlRootPW

Update Script

Add the following script to crontab to update time zones daily:

#!/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
Additional Tips

Time zone information takes up about 5 MB in MySQL.
Avoid dropping time zone tables; instead, use TRUNCATE.
The above is the detailed content of How Can I Effectively Use Time Zones in MySQL?. For more information, please follow other related articles on the PHP Chinese website!