convert_tz returns null

I know this sounds stupid, but when I use

SELECT CONVERT_TZ('2004-01-01 12:00:00','UTC','Asia/Jakarta') AS time

it outputs NULL. I'm using MySQL Workbench in Ubuntu 12.04 64 bit, and it works in my other laptop/os (also using MySQL Workbench).

Solution 1:

This will happen if you haven't loaded the time zone table into mysql.

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

mysql is the name of the built-in database that holds MySQL-specific configuration data.

Solution 2:

I found this thread after spending some time trying to figure out why after running the command in the accepted answer (which is the same on MySQL's dev site) the command was unable to convert between timezones such as

SELECT CONVERT_TZ('2004-01-01 12:00:00','UTC','MET') AS time

It turns out that on OS X there are two files that cause problems: /usr/share/zoneinfo/Factory and /usr/share/zoneinfo/+VERSION.

The fix... temporarily moving these files to a different location such as /usr/share/zoneinfo/.bak/ allows for the command

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

to fully populate all of the expected timezone information.

This may or may not be a bug in my installed version of MySQL:

$ mysql --version
mysql  Ver 14.14 Distrib 5.6.11, for osx10.6 (x86_64) using  EditLine wrapper

I am also operating in STRICT_MODE.

In any case, I hope this saves a few headaches for anyone searching for the fix.

Solution 3:

Apart from Windows environment, You can set Time Zone by

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

In Windows environment,

1. download Time zone description tables from http://dev.mysql.com/downloads/timezones.html

2. Stop MySQL server

3. Put then inside Mysql installation package (ie. C:\Program Files\MySQL\data\mysql)`

4. Start MySQL server

..Your work is finished..

If still you are getting NULL for CONVERT_TZ Download these database tables and insert it into mysql database http://www.4shared.com/folder/Toba2qu-/Mysql_timezone.html

Now you problem will be solved.. :)