Menu
I am attempting to load timezones into my MySql instance (that is running on Ubuntu) so that I can use the CONVERT_TZ function but when I run the command below:
mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql I get the following output and when I try to use CONVERT_TZ it just returns NULL still:
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. Warning: Unable to load '/usr/share/zoneinfo/iso3166.tab' as time zone. Skipping it. Warning: Unable to load '/usr/share/zoneinfo/posix/Asia/Riyadh87' as time zone. Skipping it. Warning: Unable to load '/usr/share/zoneinfo/posix/Asia/Riyadh88' as time zone. Skipping it. Warning: Unable to load '/usr/share/zoneinfo/posix/Asia/Riyadh89' as time zone. Skipping it. Warning: Unable to load '/usr/share/zoneinfo/posix/Mideast/Riyadh87' as time zone. Skipping it. Warning: Unable to load '/usr/share/zoneinfo/posix/Mideast/Riyadh88' as time zone. Skipping it. Warning: Unable to load '/usr/share/zoneinfo/posix/Mideast/Riyadh89' as time zone. Skipping it. Warning: Unable to load '/usr/share/zoneinfo/right/Asia/Riyadh87' as time zone. Skipping it. Warning: Unable to load '/usr/share/zoneinfo/right/Asia/Riyadh88' as time zone. Skipping it. Warning: Unable to load '/usr/share/zoneinfo/right/Asia/Riyadh89' as time zone. Skipping it. Warning: Unable to load '/usr/share/zoneinfo/right/Mideast/Riyadh87' as time zone. Skipping it. Warning: Unable to load '/usr/share/zoneinfo/right/Mideast/Riyadh88' as time zone. Skipping it. Warning: Unable to load '/usr/share/zoneinfo/right/Mideast/Riyadh89' as time zone. Skipping it. Warning: Unable to load '/usr/share/zoneinfo/zone.tab' as time zone. Skipping it.
Does anyone have an idea why this would be happening?
PS:
I'm using the following method to convert datetimes:
CONVERT_TZ(NOW(),'UTC', 'PCT') Is it possible that I'm just converting incorrectly and that is the cause of the NULL results?
472
To do this, go to Settings > Privacy > Location Services > System Services and turn on Setting Time Zone. Check that your device shows the correct time zone in Settings > General > Date & Time > Time Zone.
Non-administrator users cannot change or interact with the Set time zone automatically setting. The setting is either not visible or is "greyed out" in the Settings app. This is by design as the Set time zone automatically setting is a system wide setting that applies to all user profiles on a machine.
If you need to indicate that a time is in a certain time zone, the simplest way to do it is to put the time zone abbreviation after the time: for Eastern Standard Time, write “4:30 p.m. EST.”
This is a bug with the timezone files for Riyadh* on Debian:
http://bugs.mysql.com/bug.php?id=20545
The recommended workaround is to simply ignore the warning (if you don't need those timezone of course):
mysql_tzinfo_to_sql /usr/share/lib/zoneinfo | mysql -uroot --force mysql After importing TZ data with the --force option, you will be able to use CONVERT_TZ. Here is a transcription of what I've just done:
sh$ mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -uroot -p mysql --force 2> /dev/null Enter password: ******** sh$ mysql -p Enter password: ******** Server version: 5.1.49-3 (Debian) mysql> select convert_tz(now(), 'UTC', 'CET'); +---------------------------------+ | convert_tz(now(), 'UTC', 'CET') | +---------------------------------+ | 2013-08-13 21:04:34 | +---------------------------------+ 1 row in set (0.00 sec) Unfortunately, Sylvain's solution didn't worked for me.
I was able to populate the timezone tables of our production server by exporting the tables from the PHPMyAdmin of my development computer (on Windows) and tweaking the creation script a little bit.
Since the script is too long to be shared in a post, I will host it on my Google Drive.
The install script (with new lines and comments stripped):
https://drive.google.com/file/d/0B7iwL5F-hwr_YkItRXk2Z1VZdlE/view?usp=sharing
The script with comments:
https://drive.google.com/file/d/0B7iwL5F-hwr_dWdjTDREcXNHQmM/view?usp=sharing
Make sure to use the version without comments. The comment version take about 5 minutes to run and end up failing for some reason while the stripped version take less than 5 seconds to run.
Note: You'll probably need to run as root in order for this to work.
36
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
