5.1.12 MySQL Server Time Zone Support

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.

Note

If set to SYSTEM, every MySQL function
call that requires a timezone calculation makes a system
library call to determine the current system timezone. This
call may be protected by a global mutex, resulting in
contention.

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:

default-time-zone='timezone'

If you have the
SYSTEM_VARIABLES_ADMIN or
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

Several tables in the mysql system database
exist to maintain time zone information (see
Section 5.3, “The mysql System Database”). The MySQL installation
procedure creates the time zone tables, but does not load them.
You must do so manually using the following instructions.

Note

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 macOS. 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.

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_filetz_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:

http://dev.mysql.com/downloads/timezones.html

Download a time zone package that contains SQL statements and
unpack it, then load the package file contents into the time zone
tables:

shell> mysql -u root mysql < file_name

Then restart the server.

Warning

Do not use a downloadable package that
contains MyISAM tables. MySQL uses
InnoDB for the time zone tables. Trying to
replace them with MyISAM tables will cause
problems.

Warning

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.

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.

*sigh*

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.

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

EST5EDTCST6CDTMST7MDTPST8PDT

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.