Updating MySQL’s timezone table

Working on a server with a different timezone that my current location, I ran into a problem when a date check was causing problems with users in my zone. I needed to update timezone within the query to handle this problem. I quickly found the CONVERT_TZ() function in MySQL, but during my tests I would get NULL values returned. It states that the function will return NULL if the arguments are invalid. I was confused. Doing some more digging, I saw that it references a time zone table within MySQL. So, the next step was to populate this table.

To fill the timezone table with the latest zone information, you will need to execute the following binary and argument

mysql_tzinfo_to_sql /usr/share/zoneinfo/

This will populate the table from your zoneinfo directory. I went ahead and created a cron to do this on a monthly basis. Next to perform the query, you could execute something like this

SELECT CONVERT_TZ(NOW(),'UTC','-1:00');

To fill the timezone table with the latest zone information, you will need to execute the following binary and argument

mysql_tzinfo_to_sql /usr/share/zoneinfo/

This will populate the table from your zoneinfo directory. I went ahead and created a cron to do this on a monthly basis. Next to perform the query, you could execute something like this

SELECT CONVERT_TZ(NOW(),'UTC','-1:00');