[SOLVED] Date/time trouble in query: Fenway...Are you out there?!

[markkanning]

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?

[fenway]

TIMEDIFF only takes time fields as arguments, don't concat.

[markkanning]

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.

[markkanning]

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.

[fenway]

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().

[markkanning]

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!

[fenway]

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.