markkanning Posted September 25, 2007 Share Posted September 25, 2007 Hey Fenway(or any interested party), Do you recall this thread from yesterday: http://www.phpfreaks.com/forums/index.php/topic,160655.0.html? Anyway, per your suggestions, I've cobble together yet another query...that gives no answer! Am I using these MySQL functions in the wrong way? //PHP VARS USED IN QUERY $hoursdif = 2; $timezoneoffset = 60*60*$hoursdif; $now = date('Y-m-d H:i:s', time() + $timezoneoffset); $today = date('Y-m-d', time() + $timezoneoffset); //QUERY SELECT * FROM table WHERE '".$now."' -> BETWEEN CONCAT(' ', '".$today."', StartTime) -> AND DATE_ADD(CONCAT(' ', '".$today."', StartTime), INTERVAL TIMEDIFF(CONCAT(' ', '".$today."', EndTime), CONCAT(' ', '".$today."', StartTime)) HOUR) The logic of the query being: Find all where "now"(full Unix Time Stamp) is between "StartTime"(with the "today" var amended to it for a day reference) and "EndTime"(defined as the StartTime with the "today" var PLUS the difference between "EndTime" and "StartTime"). My feeling is it has something to do with the interval declaration: INTERVAL TIMEDIFF(CONCAT(' ', '".$today."', EndTime), CONCAT(' ', '".$today."', StartTime)) HOUR AM I pushin' the limits? Bad karma, maybe? What's my hangup, fellow freaks? Quote Link to comment Share on other sites More sharing options...
fenway Posted September 25, 2007 Share Posted September 25, 2007 TIMEDIFF only takes time fields as arguments, don't concat. Quote Link to comment Share on other sites More sharing options...
markkanning Posted September 25, 2007 Author Share Posted September 25, 2007 I boiled it down to this with no success(note the change in the "now" var): //PHP VARS USED IN QUERY $hoursdif = 2; $timezoneoffset = 60*60*$hoursdif; //***NEW "NOW" - TIME ONLY!!! $now = date('H:i:s', time() + $timezoneoffset); //QUERY SELECT * FROM table WHERE '".$now."' -> BETWEEN StartTime -> AND DATE_ADD(StartTime, INTERVAL TIMEDIFF(EndTime, StartTime) HOUR) I'm wondering...will "TIMEDIFF(EndTime, StartTime)" produce a single number, like "5" or "10", or will it produce something like "05:00:00" if that's the format of the data? If the latter is true, can you use that in an interval declaration of the DATE_ADD() function? By the way...using the last query before this one, I tried changing TIMEDIFF() to DATEDIFF() to correspond to the data I was parsing. No go. Quote Link to comment Share on other sites More sharing options...
markkanning Posted September 25, 2007 Author Share Posted September 25, 2007 To answer my own question, but not solving the overarching problem, I've just read about the different ways you can express intervals in a DATE_ADD() function. To speak to the possibility of "TIMEDIFF(EndTime, StartTime)" resulting in something like "05:00:00", I changed the DATE_ADD() to read "DATE_ADD(jockStartTime, INTERVAL TIMEDIFF(jockEndTime, jockStartTime) HOUR_MINUTE_SECOND)". Still no go. Quote Link to comment Share on other sites More sharing options...
fenway Posted September 25, 2007 Share Posted September 25, 2007 Let's ignore the timezone issue for a minute... yes, timediff() return HH:MM:SS, have you proved that you can use this for an interval? There's always the extract() function... and you should be using CURTIME() instead of that craziness with NOW(). Quote Link to comment Share on other sites More sharing options...
markkanning Posted September 25, 2007 Author Share Posted September 25, 2007 According to mysql.com, you can use HH:MM:SS as an interval as long as you declare it like: "INTERVAL '22:00:00' HOUR_MINUTE_SECOND". This approach didn't yield any favorable results. Also, I'm not using the MySQL function NOW() as my yardstick. I'm using a PHP variable call $now(which outputs just like a Unix time stamp) that has to have a timezone offset amended to it as the server time is timezones away from the region that the website is marketing for. By that virtue, CURTIME() would also be inaccurate. I suppose I could refer to it as DATE_ADD(CURTIME(), INTERVAL 2 HOUR), but then it just becomes a 6-or-one-half-dozen situation. Speaking of time, much of it has gotten away from me. The beancounters and taskmasters are clamoring for results. I'm just going to have to fall back on the method I was avoiding. Thanks for all your help, Fenway! Quote Link to comment Share on other sites More sharing options...
fenway Posted September 25, 2007 Share Posted September 25, 2007 According to mysql.com, you can use HH:MM:SS as an interval as long as you declare it like: "INTERVAL '22:00:00' HOUR_MINUTE_SECOND". This approach didn't yield any favorable results. Also, I'm not using the MySQL function NOW() as my yardstick. I'm using a PHP variable call $now(which outputs just like a Unix time stamp) that has to have a timezone offset amended to it as the server time is timezones away from the region that the website is marketing for. By that virtue, CURTIME() would also be inaccurate. I suppose I could refer to it as DATE_ADD(CURTIME(), INTERVAL 2 HOUR), but then it just becomes a 6-or-one-half-dozen situation. Speaking of time, much of it has gotten away from me. The beancounters and taskmasters are clamoring for results. I'm just going to have to fall back on the method I was avoiding. Thanks for all your help, Fenway! If you have to let it go, then so be it... I've been pre-occupied on my end, too, and haven't been able to actually run any other these on my test box... if it ever comes back, let me know. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.