liselot Posted April 5, 2014 Share Posted April 5, 2014 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 More sharing options...
Larry Posted April 7, 2014 Share Posted April 7, 2014 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 More sharing options...
liselot Posted April 8, 2014 Author Share Posted April 8, 2014 I tried SELECT CONVERT_TZ(NOW(),'Europe/Amsterdam','UTC') The result is NULL Link to comment Share on other sites More sharing options...
Larry Posted April 12, 2014 Share Posted April 12, 2014 That is surprising. What happens when you run this query: SELECT COUNT(*) FROM mysql.time_zone_name Link to comment Share on other sites More sharing options...
liselot Posted April 12, 2014 Author Share Posted April 12, 2014 I get COUNT(*) = 474 Link to comment Share on other sites More sharing options...
Antonio Conte Posted April 14, 2014 Share Posted April 14, 2014 I prefer to save all dates as UTC and use DateTime objects with DateTimezone objects to display it correctly in PHP. Is that a possible solution for you? Link to comment Share on other sites More sharing options...
Larry Posted April 14, 2014 Share Posted April 14, 2014 Wow. Okay, I'm at a bit of a loss as to why the MySQL conversion is not working. What happens when you run this query: select * from mysql.time_zone_name where name like '%Amsterdam% ' Link to comment Share on other sites More sharing options...
liselot Posted April 15, 2014 Author Share Posted April 15, 2014 This results in: Name = Europe/Amsterdam, Time_zone_id = 415 Link to comment Share on other sites More sharing options...
Larry Posted April 17, 2014 Share Posted April 17, 2014 Wow. I'm really stymied here. I'll keep mulling this over, but I'm pretty stumped, as it seems like it should work. What version of MySQL are you running on what OS? Link to comment Share on other sites More sharing options...
liselot Posted April 17, 2014 Author Share Posted April 17, 2014 I'm running MySQL Server version: 5.6.15 - MySQL Community Server (GPL) on Mac OS X 10.9.2 Link to comment Share on other sites More sharing options...
liselot Posted April 17, 2014 Author Share Posted April 17, 2014 To Antonio: thank you for your suggestion, I will keep this in mind. Liselot Link to comment Share on other sites More sharing options...
Larry Posted April 25, 2014 Share Posted April 25, 2014 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 More sharing options...
Recommended Posts