MySQL Time Zones

Solution 1:

By default, (at least on Debian-based installations) no time zone data is loaded into MySQL. If you want to test if they are loaded, try executing:

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

If it returns a DATETIME (in this case 2012-06-07 08:00:00), you have time zones loaded. If it returns NULL, they aren't. When not loaded, you are limited to converting using offsets (e.g. +10:00 or -6:00).

This should work fine in many cases, but there are times when it is better to use named time zones, like for not worrying about daylight savings time. Executing the following command loads the time zone data from the system (Unix-only. I'm not sure what the equivalent Windows command would be):

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

If you need to continually rely on MySQL time zones, the above command should be executed every time the system time zone is updated. You could also just add it to a weekly or monthly cron job to do it for you automatically.

Then, to view a list of time zones, just do the following:

USE mysql;
SELECT * FROM `time_zone_name`;

Note, the time zone info takes up about 5 MB in MySQL. If you ever want to un-load the timezone info, just execute the following and restart MySQL:

TRUNCATE `time_zone` ;
TRUNCATE `time_zone_leap_second` ;
TRUNCATE `time_zone_name` ;
TRUNCATE `time_zone_transition` ;
TRUNCATE `time_zone_transition_type` ;

Do not DROP these tables or bad things will happen.

Edit:

Based on a user comment below, if you want to have the timezones automatically updated when you update the system, you first need to allow root to log in without being prompted for a password.

MySQL >= 5.6.6

Execute the following [source]:

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

MySQL < 5.6.6

Create a ~/.my.cnf file (if it doesn't exist yet) and add the following:

[client]
user=root
password=yourMysqlRootPW

Then execute chmod 600 ~/.my.cnf to make sure nobody else can read it.

Update script

Add the following script to crontab to be executed once per day:

#!/bin/bash
# Find if there are any timezone files that have been modified in the last 24   
# hours and do not have ".tab" in the name (since these are not timezone files) 
if [ `find /usr/share/zoneinfo -mtime -1 | grep -v '\.tab' | wc -l` -gt 0 ]; then
    echo "Updating MySQL timezone info"
    # Note, suppressing STDERR here because of the .tab files above
    # that cause warnings.
    mysql_tzinfo_to_sql /usr/share/zoneinfo 2>/dev/null | mysql -u root mysql
    echo "Done!\n"
fi

Remove the echo lines if you don't want any output.

Note: This is (mostly) untested. Let me know if you have any issues and I'll update this answer.

Solution 2:

From the MySQL 5.7 documentation (emphasis mine):

timezone values can be given in several formats, none of which are case sensitive:

The value 'SYSTEM' indicates that the time zone should be the same as the system time zone.

The value can be given as a string indicating an offset from UTC, such as '+10:00' or '-6:00'.

The value can be given as a named time zone, such as 'Europe/Helsinki', 'US/Eastern', or 'MET'. Named time zones can be used only if the time zone information tables in the mysql database have been created and populated.

It should be noted that the MySQL timezone variable's default setting is SYSTEM at MySQL startup. The SYSTEM value is obtained from an operating system setting (e.g. from the file that is referenced by the symlink /etc/localtime)

MySQL's default timezone variable can be initialised to a different value at start-up by providing the following command line option:

--default-time-zone=timezone

Alternatively, if you are supplying the value in an options file, you should use the following syntax to set the variable:

--default-time-zone='timezone'

If you are a MySQL SUPER user, you can set the SYSTEM time_zone variable at runtime from the MYSQL> prompt using the following syntax:

SET GLOBAL time_zone=timezone;

MySQL also supports individual SESSION timezone values which defaults to the GLOBAL time_zone environment variable value. To change the session timezone value during a SESSION, use the following syntax:

SET time_zone=timezone;

In order to interrogate the existing MYSQL timezone setting values, you can execute the following SQL to obtain these values:

SELECT @@global.time_zone, @@session.time_zone;

For what it's worth, I simply googled mysql time_zone configuration valid values and looked at the first result.

Solution 3:

You can run this query to get a complete list of the timezones MySQL supports:

SELECT Name
FROM mysql.time_zone_name
ORDER BY Name

(Found on this page: https://www.plumislandmedia.net/mysql/time-zones-mysql/)

Solution 4:

An exhaustive list of timezones can be downloaded from MySQL's website as database tables that get imported into MySQL server.

For Calgary time you can specify UTC offsets as

set time_zone = '-6:00';