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

  • 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

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
 Share

×
×
  • Create New...