Gary Gary - 4 months ago 36
SQL Question

MySQL Time Zones

Is there an exhaustive list of MySQL Time Zones?

It seems that the valid values for

time_zone
in MySQL settings are dependent on the host Operating System but I have been unable to find a list of possible values.

I need the time to show Calgary local time.

Answer

From the 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.***time_zone* variable

It should be noted that the MySQL timezone variable's default setting is SYSTEM at MySQL startup. The SYSTEM value is obtained from the the operating system's GLOBAL time_zone environment variable.

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.

Comments