tz command returns NULL

2018-06-08 06:16:48

I have a problem with convert_tz returning null in mysql.

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

i read the manual http://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html i ran this command: bash-3.2# mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql Enter password: Warning: Unable to load '/usr/share/zoneinfo/+VERSION' as time zone. skipping it.

Warning: Unable to load '/usr/share/zoneinfo/Asia/Riyadh87' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/Asia/Riyadh88' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/Asia/Riyadh89' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/Mideast/Riyadh87' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/Mideast/Riyadh88' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/Mideast/Riyadh89' as time zone. Skipping it.
ERROR 1406 (22001) at line 38916: Data too long for column 'Abbreviation' at row 1

then, using mysql i ran this command. SELECT CONVERT_TZ('2004-01-01 12:00:00','GMT','EST'); which returns null. I can confirm the mysql.time_zone_transition_type table has GMT and EST entries.

Another way to get around this problem, export the mysql_tzinfo to a textfile for editing:

$ mysql_tzinfo_to_sql /usr/share/zoneinfo > tzinfo.sql

Find the problematic line, in my case it was line 38982, which was a splitted query from line 38982 to 38984:

INSERT INTO time_zone_transition_type (Time_zone_id, Transition_type_id, Offset, Is_DST,    Abbreviation) VALUES (@time_zone_id, 0, 0, 0, 'Local time zone must be set--see zic manual page');

Change it to:

INSERT INTO time_zone_transition_type (Time_zone_id, Transition_type_id, Offset, Is_DST, Abbreviation) VALUES (@time_zone_id, 0, 0, 0, 'UNSET');

Save the file and insert it to your mysql database:

$ cat tzinfo.sql | mysql -u root mysql

Source: http://dev.mysql.com/doc/refman/4.1/en/time-zone-support.html
Tested with MacOS 10.9.3, MySQL 5.6.15

**更新，我解决了这个问题，使用一个Linux框创建sql文件：mysql_tzinfo_to_sql / usr / share / zoneinfo，然后将这个文件导入到我的mac mysql中。

链接地址: http://www.djcxy.com/p/25050.html

上一篇: TZ不适用于2次相同的转换

下一篇: tz命令返回NULL