convert_tz returns null

Asked 22/1, 2013 at 8:25 Answered 1/11, 2019 at 12:34

134

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).

Souter answered 22/1, 2013 at 8:25 Comment(0)

243

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.

Destructive answered 22/1, 2013 at 8:37 Comment(20)

Do you just need to do this once, or every time that MySql starts up? – Amelioration 13/8, 2013 at 17:9

Just once. It loads the information into a table into MySQL, which is used from then on. – Destructive 13/8, 2013 at 17:10

You can use the force flag to override any errors you run into as well: mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root --force -p mysql – Ashore 22/3, 2014 at 14:4

I needed to restart the MySQL daemon too before it would come into effect (on Debian). – Simms 17/7, 2014 at 8:50

doesn't work for me :-/ I get a password prompt and then "access denied". Can someone explain what that command is intended to do – Sideway 8/8, 2014 at 18:39

It updates the timezone information in the MySQL configuration. Only the database administrator should be able to do this, you need to enter the password for the MySQL root user. – Destructive 8/8, 2014 at 18:45

For a one off query, if you don't feel like messing with the tables, you can also specify the two zones by their +/- equivalents: e.g. SELECT CONVERT_TZ(created_at,'+00:00','-05:00') as created FROM table – Offensive 17/10, 2014 at 18:54

@Destructive Can you provide guidance on how to set this for a MySQL instance on Google cloud DB? – Colligan 29/11, 2014 at 6:53

I have absolutely no idea how to do it there. I'm surprised they don't load it by default. – Destructive 29/11, 2014 at 7:52

@DebockD. Interesting, I didn't need to restart the daemon on CentOS – Bodega 31/3, 2015 at 15:49

I get this: ERROR 1406 (22001) at line 39339: Data too long for column 'Abbreviation' at row 1 – Puckett 7/7, 2015 at 23:22

@Puckett MySQL time zone abbreviations are limited to 8 characters. Your zoneinfo directory must have a time zone with an abbreviation that's too long. Redirect the output of mysql_tzinfo_to_sql /usr/share/zoneinfo to a file, find the line with the long abbreviation, and edit it. – Destructive 8/7, 2015 at 4:10

Hi, when I run this command I got error like this : Warning: Unable to load '/usr/share/zoneinfo/iso3166.tab' as time zone. Skipping it. Warning: Unable to load '/usr/share/zoneinfo/zone.tab' as time zone. Skipping it. – Humdrum 21/10, 2015 at 11:25

@GhanshyamKatriya Those don't look like real time zones, you can probably ignore these warnings. – Destructive 21/10, 2015 at 11:27

thanks @Destructive for your quick reply. but still I am not able to convert UTC/GMT to CST/PDT timezone. Can u plz help me? – Humdrum 21/10, 2015 at 12:17

It sounds like your zoneinfo database may be corrupt or incomplete. Download the package from the link in the documentation – Destructive 21/10, 2015 at 12:26

On a Galera cluster with 3 nodes, the timezones get erased from the mysql table, any comment/help ? – Kingsley 3/10, 2018 at 21:22

Sorry, I don't know anything about Galera. – Destructive 3/10, 2018 at 21:24

Just in case - last mysql stands for database name, you should keep it. Because first I thought this is a password, so I replaced it and the I was getting error no database selected – Bliss 12/1, 2020 at 9:45

@TheGodfather Yeah, it's not obvious. That's why I always like to use --database mysql to be super explicit. – Cha 16/9, 2020 at 14:0

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.

Authorization answered 13/8, 2013 at 17:1 Comment(1)

this is a good solution BUT on OSX you will not be able to modify (move) any files under /usr/share due to SIP (System Integrity Protection). I did not want to disable SIP and ended up performing the following workaround: mysql_tzinfo_to_sql /usr/share/zoneinfo > zone_import.sql cat zone_import.sql | mysql -u root mysql --password=mypass This will skip problematic files such as +VERSION without having to move them and restore – Reinwald 2/1, 2023 at 10:32

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.. :)

Ridings answered 15/3, 2014 at 9:10 Comment(0)

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/

Noach answered 14/11, 2016 at 5:44 Comment(1)

Other than the location of the binary folder, this applies to macOS in general. Thanks! – Discourtesy 31/3, 2020 at 22:40

These are the steps to make it work if you're in windows and using MySQL 5.7.

Right click on My Computer/Computer/This PC or whatever the name in your OS and choose Properties.
Choose "Advanced system settings" from the left panel.
Choose "Environmental Variables", enter the complete path name of your MySQL bin directory (generally it will be in, C:\Program Files\MySQL\MySQL Server 5.7\bin).
Open cmd prompt, enter into mysql using mysql -u root -p password.
Enter use mysql to select the MySQL DB.
Download the file "timezone_YYYYc_posix_sql.zip" (In the place of YYYY, substitute the maximum year available in that page like 2017 or 2018) from https://dev.mysql.com/downloads/timezones.html.
Extract it and open the file in text editor.
Copy the contents and execute in the cmd prompt.

On successful completion, you should be able to use CONVERT_TZ and other timezone functions.

Desta answered 4/12, 2017 at 17:24 Comment(0)

1) In Windows, there isn't any data folder now in C:\Program Files\MySQL\ as in other answers.

2) In that case, look for C:\ProgramData\MySQL\MySQL Server 5.x\Data\mysql. Generally this folder hidden and you will not see C:\ProgramData\ some times.

3) Change the Settings in View tab to see Hidden files and Folders as explained here https://irch.info/index.php?pg=kb.page&id=133

4) Stop the MySQL service by searching for "services" in Windows Start button.

5) Then unzip the timezone_2017c_posix.zip and then copy the files in it (copy the files directly, don't copy the whole folder itself), and paste in C:\ProgramData\MySQL\MySQLServer5.x\Data\mysql\

6) For MySQL 5.7, timezone_2017c_posix.zip will just give a .sql file after unzipping and it may not solve the issue. So go ahead and download the zip file for 5.6 even if you are running MySQL 5.7 and copy those files to C:\ProgramData\MySQL\MySQL Server 5.x\Data\mysql\

7) Restart the MySQL server. To check if the CONVERT_TZ () is working, run this sql query.

SELECT CONVERT_TZ('2004-01-01 12:00:00','UTC','Asia/Jakarta'); and check for non-null output.

Exultant answered 12/1, 2018 at 23:8 Comment(0)

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

If you don't do this, the function CONVERT_TZ won't recognize your input timezone (i.e. your examples: 'UTC','Asia/Jakarta'), and will simply return NULL.

Manly answered 27/2, 2013 at 3:28 Comment(0)

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 see: https://bugs.mysql.com/bug.php?id=68861

the fix would be to run the following

this will add a line to disable the mysql mode and allow mysql to insert truncated data this was because of a mysql bug where mysql would add a null character at the end (according to the above link)

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

Repletion answered 23/2, 2016 at 14:7 Comment(2)

I have OSX El Capitan and this is the only answer to get it to work. For future reference: in my case the error states: " Data too long for column 'Abbreviation' at row 1". Note: all efforts to do (sudo) mkdir or (sudo) mv of the files mentioned in other answers result in 'Permission denied' – Pshaw 27/3, 2017 at 6:51

BTW I didn't need to restart MySQL. – Pshaw 27/3, 2017 at 7:8

On Mac OS Catalina when using XAMPP,

Go to /Applications/XAMPP/xamppfiles/bin folder in Terminal then run following.

./mysql_tzinfo_to_sql /usr/share/zoneinfo | sed -e "s/Local time zone must be set--see zic manual page/local/" | ./mysql -u root mysql

This worked for me.

Areta answered 1/11, 2019 at 12:34 Comment(0)

Hot tags

Godot Unity Godot Help Programming Godot 4.X GUI GDScript 3D 2D Physics CSharp Godot 3.X VR XR Projects C++

Recommended topics

Hot tags