margaux Posted May 23, 2012 Share Posted May 23, 2012 I’m creating the auction site example from the last chapter of Modern Javascript. It’s a very good example to test one’s understanding of php and mysql queries. I’m struggling with the conversion of timezones. All my queries work fine except when I try to use CONVERT_TZ on any of the dates. For example the following query returns all my rows with a value for itemId, item and bid but null for the column which converts the date to the user's timezone. SELECT itemId, item, FORMAT(COALESCE(MAX(bid), openingPrice),2), IF (CONVERT_TZ(dateClosed, 'UTC', 'Asia/Tokyo') < DATE_ADD(UTC_TIMESTAMP(), INTERVAL 24 HOUR), DATE_FORMAT(dateClosed,'%l:%i %p'), DATE_FORMAT(CONVERT_TZ(dateClosed, 'UTC', 'Asia/Tokyo'), '%M %D @ %l:%i %p')) FROM items LEFT JOIN bids USING (itemId) GROUP BY itemId ORDER BY dateClosed I'm starting to think that I need to configure php or mysql in some way to deal with timezone conversion but I'm not sure in which folder to look for a timezone file. I did try entering this command in the terminal, and restarted MAMP but that did not work either. # mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql Any ideas? Link to comment Share on other sites More sharing options...
Larry Posted May 24, 2012 Share Posted May 24, 2012 Yeah, if you're getting null for the converted time zones, your MySQL doesn't support it. You don't have to do anything in PHP, you just need to enable the timezones (i.e., load them) in MySQL. As you're using a Mac (yes?), did you see this post of mine: http://www.larryullman.com/2011/05/27/utc-and-time-zone-support-in-mysql/ Link to comment Share on other sites More sharing options...
margaux Posted May 24, 2012 Author Share Posted May 24, 2012 Thanks Larry for your reply. I'm surprised that your article didn't come up when I googled mysql convert_tz. Your post was informative but I think I have an issue with mysql on my machine. I ran this command in the terminal mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql the response is -bash: mysql_tzinfo_to_sql: command not found -bash: /usr/local/mysql/bin/mysql: No such file or directory the usr/share/zoneinfo folder exists but the usr/local/mysql/bin/mysql folder does not. Do you have any suggestions on what I need to do to get this to work. I have a usr/local/bin folder but no mysql folder within it. I'm using a mac with OS X 10.6.8. Thanks for any suggestions. I would like to get the convert_tz functionality as it would be useful for some other sites I'm working on. Link to comment Share on other sites More sharing options...
Larry Posted May 25, 2012 Share Posted May 25, 2012 Sorry about that. If you're running MAMP, then you'll need to use /Applications/MAMP/Library/bin/mysql -u root -p mysql (I believe that's the right path). Link to comment Share on other sites More sharing options...
margaux Posted May 25, 2012 Author Share Posted May 25, 2012 I really thought that was going to do the trick because I can see mysql_tzinfo_to_sql in the /Applications/MAMP/Library/bin/ directory. I've tried entering it numerous times to ensure I don't have a typo. The error returned is -bash: mysql_tzinfo_to_sql: command not found Is it possible that that file is corrupt and if so can I download it from somewhere? thanks for you help. Link to comment Share on other sites More sharing options...
Larry Posted May 25, 2012 Share Posted May 25, 2012 Try this: cd /Applications/MAMP/Library/bin ./mysql_tzinfo_to_sql /usr/share/zoneinfo | ./mysql -u root -p mysql Link to comment Share on other sites More sharing options...
margaux Posted May 26, 2012 Author Share Posted May 26, 2012 This is perplexing. The first time I tried the above, I got the +VERSION error your post referred to, so I sorted that out ran the above command again and the message returned was Usage: ./mysql_tzinfo_to_sql timezonedir ./mysql_tzinfo_to_sql timezonefile timezonename ./mysql_tzinfo_to_sql --leap timezonefile which looked promising, though I don't know what that means. However, I am still getting null values when I run the CONVERT_TZ query in phpmyadmin. I've spent alot of time searching the web for similar scenarios and one suggestion was to stop and start the mysql server which after much seaching I think I accomplished but to be honest most of what is returned by the terminal commands is well and truly greek to me. If you have any more suggestions I would be grateful as I can see alot of uses for this function. For anyone who has issues which require use of terminal commands this link helped me make sense of some of what I was keying in. I used it for amongst other things, moving the +VERSION file as the /usr/ directory is locked. Now I know what sudo means! Link to comment Share on other sites More sharing options...
margaux Posted May 26, 2012 Author Share Posted May 26, 2012 I don't know what possessed me but I decided to try again - anyway lo and behold it worked! I got a bunch of warnings about the +VERSION file and some Riyadh timezones but have tested it successfully on 3 different timezones. I can only think that restarting the mysql server and my machine may have made a difference? Thanks again Larry for your help, really appreciate it. Link to comment Share on other sites More sharing options...
Larry Posted May 26, 2012 Share Posted May 26, 2012 That's excellent news. You're quite welcome and thanks for sharing your experience! Link to comment Share on other sites More sharing options...
Recommended Posts