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
Some functions are not affected. These include:
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
- If the mysql database time zone tables are being used, a named time zone, such as
Europe/Helsinki, is permissible.
mysql Time Zone Tables
The mysql database contains a number of time zone tables:
By default, these 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: