Time Zones

MariaDB keeps track of several time zone settings.

Setting the Time Zone

The time_zone system variable is the primary way to set the time zone. It can be specified in one of the following formats:

  • The default value is 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.
  • An offset from Coordinated Universal Time (UTC), such as +5:00 or -9:00, can also be used.
  • If the time zone tables in the 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.

Global Server 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 |
+---------------+--------+

Session Time Zone

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 |
+---------------+--------+

System Time Zone

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:

  • If you are starting the server with 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'
  • If you are using a Unix-like operating system, then you can set the system time zone by setting the TZ environment variable in your shell before starting the server. For example:
$ export TZ='America/New_York'
$ service mysql start
  • On some Linux operating systems, you can change the default time zone for the whole system by making the /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
  • On some Debian-based Linux operating systems, you can change the default time zone for the whole system by executing the following:
sudo dpkg-reconfigure tzdata
  • On Linux operating systems that use 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

Time Zone Effects

Time Zone Effects on Functions

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

Some functions are not affected. These include:

Time Zone Effects on Data Types

Some data types are affected by the time zone settings.

If a column uses the TIMESTAMP data type, then any inserted values are converted from the session's time zone to Coordinated Universal Time (UTC) when stored, and converted back to the session's time zone when retrieved.

The DATETIME data type is stored as-is, so it does not undergo automatic time zone conversions. However, DATETIME literals are still validated against the session's time zone. See the next section about that.

Time Zone Effects on DATETIME Literal Validation

DATETIME literals are validated against the session's time zone. For example, if a specific time range never occured in a specific time zone due to daylight savings time, then DATETIME values within that range would be invalid for that time zone.

For example, daylight savings time started on March 10, 2019 in the US, so the time range between 02:00:00 and 02:59:59 is invalid for that day in US time zones:

SET time_zone = 'America/New_York';
Query OK, 0 rows affected (0.000 sec)

INSERT INTO timestamp_test VALUES ('2019-03-10 02:55:05');
ERROR 1292 (22007): Incorrect datetime value: '2019-03-10 02:55:05' for column `db1`.`timestamp_test`.`timestamp_test` at row 1

But that same time range is fine in other time zones, such as Coordinated Universal Time (UTC). For example:

SET time_zone = 'UTC';
Query OK, 0 rows affected (0.000 sec)

INSERT INTO timestamp_test VALUES ('2019-03-10 02:55:05');
Query OK, 1 row affected (0.002 sec)

mysql Time Zone Tables

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.

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.