Home > Database > Mysql Tutorial > body text

How Can I Effectively Use Time Zones in MySQL?

DDD
Release: 2024-11-05 18:46:02
Original
254 people have browsed it

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
Copy after login

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

USE mysql;
SELECT * FROM `time_zone_name`;
Copy after login

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')
Copy after login

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
Copy after login

MySQL < 5.6.6

Create a ~/.my.cnf file:

user=root
password=yourMysqlRootPW
Copy after login

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!

source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template