MariaDB timezones ¶

https://mariadb.com/kb/en/time-zones/
https://mariadb.com/kb/en/mysql_tzinfo_to_sql/
postgresql_timezones
Date {time,timedelta}

Time Zones ¶

MariaDB keeps track of several time zone settings.

mysql Time Zone Tables ¶

The mysql database contains a number of time zone tables:

time_zone
time_zone_leap_second
time_zone_name
time_zone_transition
time_zone_transition_type

Warning

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.

mysql_tzinfo_to_sql ¶

See also

https://mariadb.com/kb/en/mysql_tzinfo_to_sql/

mysql_tzinfo_to_sql is a utility used to load time zones on systems that have a zoneinfo database to load the time zone tables (time_zone, time_zone_leap_second, time_zone_name, time_zone_transition and time_zone_transition_type) into the mysql database.

Most Linux, Mac OS X, FreeBSD and Solaris systems will have a zoneinfo database - Windows does not.

The database is commonly found in the /usr/share/zoneinfo directory, or, on Solaris, the /usr/share/lib/zoneinfo directory.

Examples ¶

Most commonly, the whole directory is passed:

            shell>mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u admin mysql

           

            Warning: Unable to load '/usr/share/zoneinfo/leap-seconds.list' as time zone. Skipping it.

           

Load a single time zone file, timezone_file, corresponding to the time zone called timezone_name:

            shell> mysql_tzinfo_to_sql timezone_file timezone_name | mysql -u admin mysql

           

A separate command for each time zone and time zone file the server needs is required.

To account for leap seconds, use:

            shell> mysql_tzinfo_to_sql --leap timezone_file | mysql -u admin mysql

           

After populating the time zone tables, you should usually restart the server so that the new time zone data is correctly loaded.

Time Zone Effects ¶

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

NOW()
SYSDATE()
CURDATE()
CURTIME()
UNIX_TIMESTAMP()

UTC_TIMESTAMP(), sysdate(), curdate(), curtime(), NOW() ¶

            SELECT UTC_TIMESTAMP(), sysdate(), curdate(), curtime(), NOW();

           

            UTC_TIMESTAMP()    |sysdate()          |curdate() |curtime()|NOW()              |
2020-04-29 14:47:12|2020-04-29 14:47:12|2020-04-29| 14:47:12|2020-04-29 14:47:12|

Setting the default_time_zone to UTC timezone (‘+00:00’) ¶

See also

https://stackoverflow.com/questions/19903610/how-to-set-permanent-default-timezone-in-mysql-server

Il est indispensable d’écrire les dates au format UTC afin de gérer les dates à un niveau international.

Pour cela, avec MySQL/MariaDB, il est nécessaire de rajouter la ligne suivante au fichier de configuration

           [mysqld]
default_time_zone='+00:00'

Updateing the MariaDB configuration file ¶

See also

Restart the MariaDB server

Voir mysql/mariadb.conf.d/50-server.cnf

After updating the configuration file we have to restart the MariaDB server:

            sudo service mariadb restart

           

UTC datetime checking ¶

            SELECT @@global.time_zone, @@session.time_zone;

           

            @@global.time_zone|@@session.time_zone|
+00:00            |+00:00             |

With UTC timezone set UTC_TIMESTAMP() is egal to NOW():

            SELECT UNIX_TIMESTAMP(), UTC_TIMESTAMP(), NOW();

           

            UNIX_TIMESTAMP()|UTC_TIMESTAMP()    |NOW()              |
      1588170375|2020-04-29 14:26:15|2020-04-29 14:26:15|

Datetime programming ¶

See also

Date {time,timedelta}