# Time Zones

MariaDB keeps track of several time zone settings.

## Setting the Time Zone

The [time\_zone](https://mariadb.com/docs/server/server-management/variables-and-modes/server-system-variables#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](https://mariadb.com/docs/server/server-management/variables-and-modes/server-system-variables#system_time_zone) system variable will be used. Note that if you are using `SYSTEM` with replication in either statement or mixed mode, you MUST use the same value for `system_time_zone` on all replicas (otherwise `TIMESTAMP` columns will not replicate correctly). See [System Time Zone](#system-time-zone) below for more information.
* An offset from [Coordinated Universal Time (UTC)](https://mariadb.com/docs/server/reference/data-types/string-data-types/character-sets/internationalization-and-localization/coordinated-universal-time), such as `+5:00` or `-9:00`, can also be used.
* If the time zone tables in the [mysql](https://mariadb.com/docs/server/reference/system-tables/the-mysql-database-tables) 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](#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](https://mariadb.com/docs/server/server-management/install-and-upgrade-mariadb/configuring-mariadb/configuring-mariadb-with-option-files#option-groups) in an [option file](https://mariadb.com/docs/server/server-management/install-and-upgrade-mariadb/configuring-mariadb/configuring-mariadb-with-option-files). For example:

```ini
[mariadb]
...
default_time_zone = 'America/New_York'
```

The global server time zone can also be changed dynamically by setting the [time\_zone](https://mariadb.com/docs/server/server-management/variables-and-modes/server-system-variables#time_zone) system variable as a user account that has the [SUPER](https://mariadb.com/docs/server/sql-statements/account-management-sql-statements/grant#super) privilege. For example:

```sql
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](https://mariadb.com/docs/server/server-management/variables-and-modes/server-system-variables#time_zone) system variable. For example:

```sql
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](https://mariadb.com/docs/server/server-management/variables-and-modes/server-system-variables#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](https://mariadb.com/docs/server/server-management/variables-and-modes/server-system-variables#time_zone) system variable. For example:

```sql
SET time_zone = 'America/New_York';
```

The current session time zone can be viewed by looking at the session value of the [time\_zone](https://mariadb.com/docs/server/server-management/variables-and-modes/server-system-variables#time_zone) system variable. For example:

```sql
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](https://mariadb.com/docs/server/server-management/variables-and-modes/server-system-variables#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 [mariadbd-safe](https://mariadb.com/docs/server/server-management/starting-and-stopping-mariadb/mariadbd-safe), then you can set the system time zone with the `--timezone` option either on the command-line or in the \[mariadbd-safe] [option group](https://mariadb.com/docs/server/server-management/install-and-upgrade-mariadb/configuring-mariadb/configuring-mariadb-with-option-files#option-groups) in an [option file](https://mariadb.com/docs/server/server-management/install-and-upgrade-mariadb/configuring-mariadb/configuring-mariadb-with-option-files). For example:

```ini
[mariadbd-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](https://mariadb.com/docs/server/server-management/install-and-upgrade-mariadb/configuring-mariadb/mariadb-environment-variables) in your shell before starting the server. For example:

```bash
$ export TZ='America/New_York'
$ service mariadb start
```

* On some Linux operating systems, you can change the default time zone for the whole system by making the [/etc/localtime](https://www.freedesktop.org/software/systemd/man/localtime.html) symbolic link point to the desired time zone. For example:

```bash
$ 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:

```bash
sudo dpkg-reconfigure tzdata
```

* On Linux operating systems that use [systemd](https://mariadb.com/docs/server/server-management/starting-and-stopping-mariadb/systemd), you can change the default time zone for the whole system by using the [timedatectl](https://www.freedesktop.org/software/systemd/man/timedatectl.html) utility. For example:

```bash
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:

* [NOW()](https://mariadb.com/docs/server/reference/sql-functions/date-time-functions/now)
* [SYSDATE()](https://mariadb.com/docs/server/reference/sql-functions/date-time-functions/sysdate)
* [CURDATE()](https://mariadb.com/docs/server/reference/sql-functions/date-time-functions/curdate)
* [CURTIME()](https://mariadb.com/docs/server/reference/sql-functions/date-time-functions/curtime)
* [UNIX\_TIMESTAMP()](https://mariadb.com/docs/server/reference/sql-functions/date-time-functions/unix_timestamp)

Some functions are not affected. These include:

* [UTC\_DATE()](https://mariadb.com/docs/server/reference/sql-functions/date-time-functions/utc_date)
* [UTC\_TIME()](https://mariadb.com/docs/server/reference/sql-functions/date-time-functions/utc_time)
* [UTC\_TIMESTAMP()](https://mariadb.com/docs/server/reference/sql-functions/date-time-functions/utc_timestamp)

### Time Zone Effects on Data Types

Some data types are affected by the time zone settings.

* [TIMESTAMP](https://mariadb.com/docs/server/reference/data-types/date-and-time-data-types/timestamp) - See [TIMESTAMP: Time Zones](https://mariadb.com/docs/server/reference/date-and-time-data-types/timestamp#time-zones) for information on how this data type is affected by time zones.
* [DATETIME](https://mariadb.com/docs/server/reference/data-types/date-and-time-data-types/datetime) - See [DATETIME: Time Zones](https://mariadb.com/docs/server/reference/date-and-time-data-types/datetime#time-zones) for information on how this data type is affected by time zones.

## mysql Time Zone Tables

The [mysql](https://mariadb.com/docs/server/reference/system-tables/the-mysql-database-tables) database contains a number of time zone tables:

* [time\_zone](https://mariadb.com/docs/server/reference/system-tables/the-mysql-database-tables/mysql-time_zone-table)
* [time\_zone\_leap\_second](https://mariadb.com/docs/server/reference/system-tables/the-mysql-database-tables/mysql-time_zone_leap_second-table)
* [time\_zone\_name](https://mariadb.com/docs/server/reference/system-tables/the-mysql-database-tables/mysql-time_zone_name-table)
* [time\_zone\_transition](https://mariadb.com/docs/server/reference/system-tables/the-mysql-database-tables/mysql-time_zone_transition-table)
* [time\_zone\_transition\_type](https://mariadb.com/docs/server/reference/system-tables/the-mysql-database-tables/mysql-time_zone_transition_type-table)

By default, these time zone tables in the [mysql](https://mariadb.com/docs/server/reference/system-tables/the-mysql-database-tables) database are created, but not populated.

If you are using a Unix-like operating system, then you can populate these tables using the [mariadb-tzinfo-to-sql](https://mariadb.com/docs/server/clients-and-utilities/administrative-tools/mariadb-tzinfo-to-sql) utility, which uses the [zoneinfo](https://linux.die.net/man/5/tzfile) 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. These are available at [MariaDB mirrors](https://mirror.mariadb.org/zoneinfo/).

Time zone data needs to be updated on occasion. When that happens, the time zone tables may need to be reloaded.

## See Also

* [LinuxJedi in Spacetime: Properly Handling Time and Date](https://www.youtube.com/watch?v=IV8q_mbZzEo) (video)

<sub>*This page is licensed: CC BY-SA / Gnu FDL*</sub>

{% @marketo/form formId="4316" %}