How to Work with Time Zones in MySQL?

MySQL Time Zones

MySQL provides support for a wide range of time zones, enabling you to store and manipulate dates and times in various geographical regions.

List of Time Zones

MySQL >= 5.7

To view an exhaustive list of MySQL time zones, execute the following query:

<code class="sql">SELECT * FROM mysql.time_zone_name;</code>

MySQL < 5.7

To load the time zone data from the system and view the list, run these commands:

<code class="bash">mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql

<code class="sql">USE mysql;
SELECT * FROM `time_zone_name`;

Setting Calgary Local Time

To display time in Calgary local time, you can set the time_zone system variable:

<code class="sql">SET time_zone = 'America/Edmonton';

Note: Replace 'America/Edmonton' with the appropriate time zone identifier for Calgary.

Troubleshooting

If you find that time zones are not available in MySQL, execute the following command:

<code class="bash">mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql

This command loads the system time zone data into MySQL.

Regular Updates

To ensure that MySQL time zones are automatically updated when the system time zones change, add the following script to a cron job:

#!/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!"
fi
The above is the detailed content of How to Work with Time Zones in MySQL?. For more information, please follow other related articles on the PHP Chinese website!