The Ultimate Guide to High Availability with MariaDB

Time Zones

You are viewing an old version of this article. View the current version here.

Contents

  1. System Time Zone
  2. Server Time Zone
  3. Connection Time Zones
  4. Time zone effects
  5. Setting the time zone
  6. mysql time zone tables

MariaDB keeps track of several time zone settings:

System Time Zone

The system time zone is determined when the server starts. It is usually read from the host machine, and then used to set the system_time_zone system variable. It can be set with the --timezone=timezone option to mysqld_safe, or by setting the TZ environment variable before starting the server.

Server Time Zone

The server time zone is by default taken from the server time zone, and sets the time_zone system variable. It can be determined by setting the --default-time-zone=timezone option at startup, or changed at runtime by a user with the SUPER privilege by running:

SET GLOBAL time_zone = timezone;

Connection Time Zones

Each client connecting will also have its own time zone. This initially comes from the global value of the time_zone system variable, which sets the session value of the same variable.

The client can also change it by running:

SET time_zone = timezone;

Time zone effects

Some functions are affected by the time zone settings. These include

as well as values stored and retrieved from TIMESTAMP columns. The latter are converted to UTC (Coordinated Universal Time) when stored, and converted back when retrieved.

Some functions are not affected. These include:

as well as DATETIME, DATE and TIME columns.

Setting the time zone

The current time zone can be viewed by looking at the global or session contents of the time_zone system variable.

SHOW GLOBAL VARIABLES LIKE 'time_zone';
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| time_zone     | SYSTEM |
+---------------+--------+

The time zone can be specified in one of the following formats:

  • SYSTEM, the default, indicating that system time zone is being used.
  • An offset from UTC, such as +5:00 or -9:00.
  • If the mysql database time zone tables are being used, a named time zone, such as Africa/Johannesburg or Europe/Helsinki, is permissible.

mysql time zone tables

By default, the mysql time zone tables are created, but not populated. It is usually preferable for the system to handle the time zone, but you can populate the mysql time zone tables using the mysql_tzinfo_to_sql utility, which uses the zoneinfo data available on Linux, Mac OS X, FreeBSD and Solaris. Windows users will need to manually import a time zone (available at http://dev.mysql.com/downloads/timezones.html).

Time zone data updates on occasion, and the time zone tables may need to be reloaded.

To disable the SYSTEM time zone and rather use the loaded mysql time zone, add default_time_zone in the mysqld section of your configuration file, for example: 

[mysqld] 
default_time_zone=Europe/Paris

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.