Jump to content
Larry Ullman's Book Forums

Recommended Posts

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

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

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

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

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

 Share

×
×
  • Create New...