Jump to content

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


markkanning

Recommended Posts

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.