DaveCoast Posted December 26, 2012 Share Posted December 26, 2012 Help - My logged in formatted dates (closed or submitted) are NULL in my sql result set. This only happens on my shared host site (local XAMPP works fine). FYI - I did execute my SQL request with the CONVERT_TZ via phpMyAdmin and that is where I see my column value = NULL. Is there some kind of time zone feature I need to have my hosting provider enable or is this a setting I should be able to set myself or is it time to find a new provider? I'm currently testing the PHP only (non JS) version. All seems to be working fine on my local dev box (XAMPP on Win7). Just for fun - I ftp'd the site up to an Addon Domain on my shared host - MyTestSite with user01/user01pass or user02/user02pass. Here's the SQL setup: // Set the time zone if visitor is logged in $localDateClosed = setItDateClosedTimeZone(); $sql = " SELECT itId, itItem, COALESCE(MAX(btBid), itOpeningPrice) as sqlPrice, IF($localDateClosed < DATE_ADD(UTC_TIMESTAMP(), INTERVAL 24 HOUR), DATE_FORMAT($localDateClosed, '%l:%i %p'), DATE_FORMAT($localDateClosed, '%M %D @ %l:%i %p')) as sqlClose FROM ch15itemtable LEFT JOIN ch15bidtable on itId = btItemId WHERE itDateClosed > UTC_TIMESTAMP() GROUP BY itID ORDER BY itDateClosed ASC"; Here's the timezone setup code: <?php function setItDateClosedTimeZone() { if (isset($_SESSION['utTimeZone'])) { $tz_column = "CONVERT_TZ(itDateClosed, 'UTC', '{$_SESSION['utTimeZone']}')"; } else { $tz_column = 'itDateClosed'; } return $tz_column; } function setBtDateSubmittedTimeZone() { if (isset($_SESSION['utTimeZone'])) { $tz_column = "CONVERT_TZ(btDateSubmitted, 'UTC', '{$_SESSION['utTimeZone']}')"; } else { $tz_column = 'btDateSubmitted'; } return $tz_column; } ?> Thanks - I appreciate the knowledge gleaned from this forum, Dave Link to comment Share on other sites More sharing options...
Larry Posted December 26, 2012 Share Posted December 26, 2012 Yes, your hunch is correct (and I'm pretty sure I say this in the book): time zone support must be enabled in MySQL on the server. Link to comment Share on other sites More sharing options...
DaveCoast Posted December 27, 2012 Author Share Posted December 27, 2012 Finally got it resolved with my hosting provider - 10 hours and painfully trying to get them to understand the issue. The solution is granting SELECT access to table mysql.time_zone for all of my MySQL accounts. Now it's time to progressively enhance my auction site with the spiffy stuff. Link to comment Share on other sites More sharing options...
Larry Posted December 29, 2012 Share Posted December 29, 2012 Glad it's working and thanks for sharing your solution! Link to comment Share on other sites More sharing options...
Recommended Posts