Is CONVERT_TZ() returning null?

Circumstances

Sample Mysql query.

SELECT CONVERT_TZ(restrictionStartTime) FROM Restrictions

I have added test code(jest) in order to check calendar after booking. This is made for observing zoneRestriction and freeBusy.

At that point, I noticed that the "CONVERT_TZ" query returns null even though there is data for restrictionStartTime in the Restrictions table.

This is because local DB cannot execute sql of "CONVERT_TZ()".

To solve this, we should load the time zone table into mysql.

There are two different way depends on operating system.

Solution

In Window

1. download Time zone description tables from http://dev.mysql.com/downloads/timezones.html (timezone_2024a_posix_sql.zip)
2. Stop MySQL server
3. Open cmd(Mysql must be set to the path of the environment variable.)
4. Go to folder which is downloaded Time zone description tables
5. Execute cmd like as below procedure

D:\MySQL_FILES\timezone_2018g_posix_sql>mysql -u root -p
Enter password: ****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 19
Server version: 8.0.13 MySQL Community Server - GPL

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| test_db            |
| world              |
+--------------------+
7 rows in set (0.00 sec)

mysql> use mysql;
Database changed
mysql> source timezone_posix.sql

After script completes successfully run some tests.

mysql> select sysdate();
+---------------------+
| sysdate()           |
+---------------------+
| 2024-04-19 00:22:06 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT CONVERT_TZ(SYSDATE(),'utc','America/Los_Angeles') utc_la;
+---------------------+
| utc_la              |
+---------------------+
| 2024-04-18 17:22:36 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT CONVERT_TZ(SYSDATE(),'utc','America/New_York') utc_ny;
+---------------------+
| utc_ny              |
+---------------------+
| 2024-04-18 20:22:44 |
+---------------------+
1 row in set (0.00 sec)

If this test data is not null, It's completed.

In else(Mac, Linux etc.)

execute

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql

There are might be many warnings. But ignore that.

References here:
https://stackoverflow.com/questions/14454304/convert-tz-returns-null/14454465
https://dba.stackexchange.com/questions/225141/how-do-i-insert-timzone-pois-sql-into-mysql