Convert_tz returns null

Question

I know that sounds silly, but when I use

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

NULL is output. I use MySQL Workbench on Ubuntu 12.04 64 bit and it works on my other laptop / os (also using MySQL Workbench).

Answer 1

This will happen if you did not load the timezone table in mysql.

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

Answer 2

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

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

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

Fix ... temporarily moving these files to another location, for example /usr/share/zoneinfo/.bak/ , allows you to execute the command

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

to completely fill out all expected time zone information.

This may or may not be an error 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 also work at STRICT_MODE .

In any case, I hope this will save a few headaches for anyone who is looking for a fix.

Answer 3

In addition to the Windows environment, you can set the time zone to

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

In a 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 (i.e. C: \ Program Files \ MySQL \ data \ mysql) `

4. Start MySQL server

.. Your work is finished.

If you still get NULL for CONVERT_TZ Download these database tables and paste them into the mysql database http://www.4shared.com/folder/Toba2qu-/Mysql_timezone.html

Now the problem will be solved .. :)

Answer 4

If you are using MySql on Windows, you need to load the time zone data into the mysql schema. Here is a good HOWTO: http://www.geeksengine.com/article/populate-time-zone-data-for-mysql.html

If you do not, the CONVERT_TZ function will not recognize your input time zone (for example, your examples: "UTC", "Asia / Jakarta") and will simply return NULL.

Answer 5

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

if you get the error data too long for column 'abbreviation' at row 1 then look: https://bugs.mysql.com/bug.php?id=68861

the correction will be as follows

this will add a line to disable mysql mode and allow mysql to insert truncated data this was due to a mysql error in which mysql will add a null character at the end (according to the link above)

 mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql (if the above gives error "data too long for column 'abbreviation' at row 1") mysql_tzinfo_to_sql /usr/share/zoneinfo > /tmp/zut.sql echo "SET SESSION SQL_MODE = '';" > /tmp/mysql_tzinfo_to.sql cat /tmp/zut.sql >> /tmp/mysql_tzinfo_to.sql mysql --defaults-file=/etc/mysql/my.cnf --user=verifiedscratch -p mysql < /tmp/mysql_tzinfo_to.sql 

Answer 6

MAMP PRO

• Open Terminal
• cd /usr/share/zoneinfo/
• sudo mv +VERSION ~/Desktop
• cd /applications/MAMP/Library/bin
• sudo ./mysql_tzinfo_to_sql /usr/share/zoneinfo | ./mysql -p -u root mysql
• sudo mv ~/Desktop/+VERSION /usr/share/zoneinfo/

Answer 7

These are the steps to get it working if you are on Windows and using MySQL 5.7.

• Right-click My Computer / Computer / This PC or any other name in your OS and select "Properties".
• Select "Advanced System Settings" in the left pane.
• Select "Environmental Variables", enter the full name of your bin bin directory (usually it will be in C: \ Program Files \ MySQL \ MySQL Server 5.7 \ bin).
• Open cmd prompt, type in mysql using mysql -u root -p password .
• Type use mysql to select the MySQL database.
• Download the file "timezone_2017c_posix_sql.zip" from https://dev.mysql.com/downloads/timezones.html .
• Extract it and open the file in a text editor.
• Copy the contents and execute on the command line.

If successful, you can use CONVERT_TZ and other time zone functions.