Documentation Home
MySQL 5.5 Reference Manual
Related Documentation Download this Manual Excerpts from this Manual

MySQL 5.5 Reference Manual  /  Globalization  /  MySQL Server Time Zone Support

10.6 MySQL Server Time Zone Support

The MySQL server maintains several time zone settings:

  • The system time zone. When the server starts, it attempts to determine the time zone of the host machine and uses it to set the system_time_zone system variable. The value does not change thereafter.

    You can set the system time zone for MySQL Server at startup with the --timezone=timezone_name option to mysqld_safe. You can also set it by setting the TZ environment variable before you start mysqld. The permissible values for --timezone or TZ are system dependent. Consult your operating system documentation to see what values are acceptable.

  • The server's current time zone. The global time_zone system variable indicates the time zone the server currently is operating in. The initial value for time_zone is 'SYSTEM', which indicates that the server time zone is the same as the system time zone.

    The initial global server time zone value can be specified explicitly at startup with the --default-time-zone=timezone option on the command line, or you can use the following line in an option file:


    If you have the SUPER privilege, you can set the global server time zone value at runtime with this statement:

    mysql> SET GLOBAL time_zone = timezone;
  • Per-connection time zones. Each client that connects has its own time zone setting, given by the session time_zone variable. Initially, the session variable takes its value from the global time_zone variable, but the client can change its own time zone with this statement:

    mysql> SET time_zone = timezone;

The current session time zone setting affects display and storage of time values that are zone-sensitive. This includes the values displayed by functions such as NOW() or CURTIME(), and values stored in and retrieved from TIMESTAMP columns. Values for TIMESTAMP columns are converted from the current time zone to UTC for storage, and from UTC to the current time zone for retrieval.

The current time zone setting does not affect values displayed by functions such as UTC_TIMESTAMP() or values in DATE, TIME, or DATETIME columns. Nor are values in those data types stored in UTC; the time zone applies for them only when converting from TIMESTAMP values. If you want locale-specific arithmetic for DATE, TIME, or DATETIME values, convert them to UTC, perform the arithmetic, and then convert back.

The current values of the global and client-specific time zones can be retrieved like this:

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

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.

Populating the Time Zone Tables

The MySQL installation procedure creates the time zone tables in the mysql database, but does not load them. You must do so manually using the following instructions.


Loading the time zone information is not necessarily a one-time operation because the information changes occasionally. When such changes occur, applications that use the old rules become out of date and you may find it necessary to reload the time zone tables to keep the information used by your MySQL server current. See the notes at the end of this section.

If your system has its own zoneinfo database (the set of files describing time zones), you should use the mysql_tzinfo_to_sql program for filling the time zone tables. Examples of such systems are Linux, FreeBSD, Solaris, and OS X. One likely location for these files is the /usr/share/zoneinfo directory. If your system does not have a zoneinfo database, you can use the downloadable package described later in this section.

The mysql_tzinfo_to_sql program is used to load the time zone tables. On the command line, pass the zoneinfo directory path name to mysql_tzinfo_to_sql and send the output into the mysql program. For example:

shell> mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql

mysql_tzinfo_to_sql reads your system's time zone files and generates SQL statements from them. mysql processes those statements to load the time zone tables.

mysql_tzinfo_to_sql also can be used to load a single time zone file or to generate leap second information:

  • To load a single time zone file tz_file that corresponds to a time zone name tz_name, invoke mysql_tzinfo_to_sql like this:

    shell> mysql_tzinfo_to_sql tz_file tz_name | mysql -u root mysql

    With this approach, you must execute a separate command to load the time zone file for each named zone that the server needs to know about.

  • If your time zone needs to account for leap seconds, initialize the leap second information like this, where tz_file is the name of your time zone file:

    shell> mysql_tzinfo_to_sql --leap tz_file | mysql -u root mysql
  • After running mysql_tzinfo_to_sql, it is best to restart the server so that it does not continue to use any previously cached time zone data.

If your system is one that has no zoneinfo database (for example, Windows), you can use a package that is available for download at the MySQL Developer Zone:

You can use either a package that contains SQL statements to populate your existing time zone tables, or a package that contains pre-built MyISAM time zone tables to replace your existing tables:

  • To use a time zone package that contains SQL statements, download and unpack it, then load the package file contents into your existing time zone tables:

    shell> mysql -u root mysql < file_name

    Then restart the server.

  • To use a time zone package that contains .frm, .MYD, and .MYI files for the MyISAM time zone tables, download and unpack it. These table files are part of the mysql database, so you should place the files in the mysql subdirectory of your MySQL server's data directory. Stop the server before doing this and restart it afterward.


Do not use a downloadable package if your system has a zoneinfo database. Use the mysql_tzinfo_to_sql utility instead. Otherwise, you may cause a difference in datetime handling between MySQL and other applications on your system.

For information about time zone settings in replication setup, please see Section 17.4.1, “Replication Features and Issues”.

Download this Manual
User Comments
  Posted by Chris Calender on March 11, 2009
After updating your OS with DST changes, you must also restart mysqld for the changes to take effect.

Restarting may not be possible at the time, so a short-term workaround (for only some cases) is to set the OS timezone back 1 hour.

Note this only works for some cases, and if you're accepting timestamped data from countries who do not observe DST yet, then the timestamps will be 1 hour off. However, if this is not the case for you, then the work-around might be a temporary solution.
  Posted by Daevid Vincent on October 19, 2010
Note that if you are trying to keep your own database of cities and their timezones, you can NOT use the mysql.time_zone_name.Time_zone_id as your FK because the key will change the next time you get a "tzdata" update and re-run "mysql_tzinfo_to_sql tz_file". You'll have to store the "Name" column instead otherwise you loose data integrity.

That script wipes the tables and re-creates them! rather than doing an UPDATE to existing records as you would reasonably expect.

  Posted by Winfred Qin on November 11, 2010
If you want to set your timezone to UTC, both '+00:00' or '-00:00' will be OK. But DO NOT use '00:00'.
  Posted by Joe Peebles on April 11, 2012
A note for Windows users:
Your MySQL Server's data directory may be in C:\ProgramData, and not in C:\Program Files. If you download the time zone description tables, and follow all the steps for stopping/starting the server and loading the files into the data directory, and see empty tables when you query any of the time zone tables, this may be your problem. Here's where my data directory was lurking:

C:\ProgramData\MySQL\MySQL Server 5.5\data\mysql
  Posted by Nigel Pearson on April 4, 2013
If the data piped from mysql_tzinfo_to_sql gives an SQL error, put it into a file first, and edit any offending lines.

e.g.: line 38408: Data too long for column 'Abbreviation' at row 1
was caused by this line generated from MacOSX 10.8's timezone files:
(@time_zone_id, 0, 0, 0, 'Local time zone must be set--see zic manual page')
which I changed to: (@time_zone_id, 0, 0, 0, 'UNSET')
  Posted by Chris Calender on April 29, 2013
"The permissible values for --timezone or TZ are system dependent."

For Windows, if you have (or create) an environment variable named TZ, some permissible values are:


From my tests, if it is daylight savings at the time the server is started, then system_time_zone will report EDT. However, if it is started when it is not daylight savings, it will report EST. In either case, "select now()" returned the correct times for me, regardless of whether it displays EDT or EST.
  Posted by Peter NOT_FOUND on September 27, 2013
Mac OSX timezone zoneinfo fix using this one-liner

mysql_tzinfo_to_sql /usr/share/zoneinfo | sed -e "s/Local time zone must be set--see zic manual page/local/" | mysql -u root mysql
  Posted by Kent Boortz on March 8, 2015
The "Local time zone must be set--see zic manual page" problem is corrected in 5.7.6-m16 (Bug#20545 and Bug#68861)
Sign Up Login You must be logged in to post a comment.