MariaDB keeps track of several time zone settings.
The time_zone
system variable is the primary way to set the time zone. It can be specified in one of the following formats:
SYSTEM
, which indicates that the system time zone defined in the system_time_zone
system variable will be used. See System Time Zone below for more information. +5:00
or -9:00
, can also be used. mysql
database were loaded, then a named time zone, such as America/New_York
, Africa/Johannesburg
, or Europe/Helsinki
, is also permissible. See mysql Time Zone Tables below for more information. There are two time zone settings that can be set within MariaDB--the global server time zone, and the time zone for your current session. There is also a third time zone setting which may be relevant--the system time zone.
The global server time zone can be changed at server startup by setting the --default-time-zone
option either on the command-line or in a server option group in an option file. For example:
[mariadb] ... default_time_zone = 'America/New_York';
The global server time zone can also be changed dynamically by setting the time_zone
system variable as a user account that has the SUPER
privilege. For example:
SET GLOBAL time_zone = 'America/New_York';
The current global server time zone can be viewed by looking at the global value of the time_zone
system variable. For example:
SHOW GLOBAL VARIABLES LIKE 'time_zone'; +---------------+--------+ | Variable_name | Value | +---------------+--------+ | time_zone | SYSTEM | +---------------+--------+
Each session that connects to the server will also have its own time zone. This time zone is initially inherited from the global value of the time_zone
system variable, which sets the session value of the same variable.
A session's time zone can be changed dynamically by setting the time_zone
system variable. For example:
SET time_zone = 'America/New_York';
The current session time zone can be viewed by looking at the session value of the time_zone
system variable. For example:
SHOW SESSION VARIABLES LIKE 'time_zone'; +---------------+--------+ | Variable_name | Value | +---------------+--------+ | time_zone | SYSTEM | +---------------+--------+
The system time zone is determined when the server starts, and it sets the value of the system_time_zone
system variable. The system time zone is usually read from the operating system's environment. You can change the system time zone in several different ways, such as:
mysqld_safe
, then you can set the system time zone with the --timezone
option either on the command-line or in the [mysqld_safe]
option group in an option file. For example: [mysqld_safe] timezone='America/New_York'
TZ
environment variable in your shell before starting the server. For example: $ export TZ='America/New_York' $ service mysql start
/etc/localtime
symbolic link point to the desired time zone. For example: $ sudo rm /etc/localtime $ sudo ln -s /usr/share/zoneinfo/America/New_York /etc/localtime
sudo dpkg-reconfigure tzdata
systemd
, you can change the default time zone for the whole system by using the timedatectl
utility. For example: sudo timedatectl set-timezone America/New_York
Some functions are affected by the time zone settings. These include:
Some functions are not affected. These include:
Some data types are affected by the time zone settings.
TIMESTAMP
- See TIMESTAMP: Time Zones for information on how this data type is effected by time zones. DATETIME
- See DATETIME: Time Zones for information on how this data type is effected by time zones. The mysql
database contains a number of time zone tables:
By default, these time zone tables in the mysql
database are created, but not populated.
If you are using a Unix-like operating system, then you can populate these tables using the mysql_tzinfo_to_sql
utility, which uses the zoneinfo
data available on Linux, Mac OS X, FreeBSD and Solaris.
If you are using Windows, then you will need to import pre-populated time zone tables. Some are available at MySQL's documentation.
Time zone data needs to be updated on occasion. When that happens, the time zone tables may need to be reloaded.
© 2019 MariaDB
Licensed under the Creative Commons Attribution 3.0 Unported License and the GNU Free Documentation License.
https://mariadb.com/kb/en/time-zones/