Jump to content
Larry Ullman's Book Forums

Recommended Posts

Hello,

 

 

I cannot convert timezones using the CONVERT_TZ() function. I get NULL as a result.

I imported the timezone information using ./mysql_tzinfo_to_sql /usr/share/zoneinfo | ./mysql -u root -p mysql after first removing the +VERSION file. I've stopped and restarted the MySQL-server and rebooted my system, but it didn't help.

I've checekd the number of records in the time_zone_name table with 

SELECT COUNT(*) FROM mysql.time_zone_name;

and there are 474 records found.
But

SELECT message_id, subject, CONVERT_TZ(date_entered, 'UTC', 'America/New_York') AS local FROM messages ORDER BY date_entered DESC LIMIT 1;

still gives NULL as a result for local.

Can anybody help me out?

 

 

Link to comment
Share on other sites

Thanks for providing all those details. I guess I'd start by first confirming that NOW() can be converted to another timezone. This will help determine whether the problem is in the timezone conversion or the messages table.

Link to comment
Share on other sites

  • 2 weeks later...

Okay. I'm running MySQL 5.6.13 on Mac OS X 10.9.2. I ran the script to populate the time zone table, but got these messages:

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 38981: Data too long for column 'Abbreviation' at row 1
I get the same results in MySQL that you do. 
 
The error on line 38981 is an INSERT into another table. I'm wondering if that's the problem here. I'll need to do more research.
Link to comment
Share on other sites

 Share

×
×
  • Create New...