How do I manage time zones in MySQL?

MySQL Time Zones

MySQL supports a comprehensive list of time zones, enabling developers to manage and manipulate date and time data precisely. The availability of time zones in MySQL settings depends on the underlying host operating system, but there are methods for loading time zone information and accessing a complete list.

To establish local time for a specific region, such as Calgary, administrators can load time zone data from the system using the following command:

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

Once loaded, a list of available time zones can be viewed by executing:

USE mysql;
SELECT * FROM `time_zone_name`;

This will display a comprehensive list of all recognized time zones in the system.

When dealing with time zones in MySQL, it's crucial to note that time zone information consumes approximately 5 MB of database space. To unload time zone 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` ;

Dropping these tables should be avoided as it can lead to issues.

For automatic time zone updates, ensure that root login is permitted without password prompts. In MySQL versions 5.6.6 and later, execute:

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

For versions prior to 5.6.6, create a ~/.my.cnf file and include the following:

[client]
user=root
password=yourMysqlRootPW

Modify file permissions to ensure privacy. A crontab script can be added for daily time zone updates:

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

By implementing these techniques, developers can effectively manage time zones in MySQL and ensure accurate date and time handling in their applications.

The above is the detailed content of How do I manage time zones in MySQL?. For more information, please follow other related articles on the PHP Chinese website!