Mysql and timezone

By default the database use the server timezone setting.

This can be override setting a global setting in my.cnf, but it can be personalized by session.

time_zone tables

By default time_zone tables are empty.

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2017-10-12 13:57:11 |
+---------------------+
1 row in set (0.00 sec)
mysql> set time_zone='US/Central';
ERROR 1298 (HY000): Unknown or incorrect time zone: 'US/Central'

To fill them, we can use the utility mysql_tzinfo_to_sql

root@server:/usr/share/zoneinfo# mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql -p
Enter password:
Warning: Unable to load '/usr/share/zoneinfo/iso3166.tab' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/leap-seconds.list' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/zone.tab' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/zone1970.tab' as time zone. Skipping it.

After that a specific time_zone can be used globally or for a session.

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2017-10-12 14:00:35 |
+---------------------+
1 row in set (0.00 sec)
mysql> set time_zone='US/Central';
Query OK, 0 rows affected (0.00 sec)
mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2017-10-12 13:01:22 |
+---------------------+
1 row in set (0.00 sec)

References

• MySQL Server Time Zone Support

Daniel Simao (talk) 11:40, 20 July 2018 (EDT)