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"
fiAdditional 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!