Getting values that a between a time

[StealthRT]

Hey all i am trying to figure out a way to see if other employees are working the same type of hours that a user is. The time for all employees is formatted like this:

5:00 PM-10:00 PM
or
11:00 AM-4:30 PM
or
8:00 AM-3:00 PM
etc...

So lets say that a few employees work at 5 or after.. How can i check via mySQL to see if, out of the 100+ employees, that they work either at 5 or AFTER 5pm but also get off by 10pm? Of course i will already have the users time stored so that is what i can compare too agents all other employees.

So if the range is = or greater than 5pm but less or equal to 10pm.

How could i do this?? :confused:

Here is the code i am working on.....

$BeginningTime = strrpos($theMon, "-");
$BeginningTime = substr($theMon, 0, $BeginningTime);
//echo $BeginningTime . " ";

$EndingTime = strrpos($theMon, "-") + 1;
$EndingTime = substr($theMon, $EndingTime);
//echo $EndingTime;

$query = "SELECT * FROM `schedule" . $Monday . "` WHERE Mon >= '$BeginningTime' AND Mon <= '$EndingTime'";

BeginningTime looks like this: 5:00 PM
EndingTime looks like this: 10:00 PM

But it keeps returning 20 rows back.. some look corrected but others are WAYYYYY off.

Thanks for your time!

David

[StealthRT]

Anyone?

David

[StealthRT]

Maybe this will help more as to what i am trying to do??

SELECT Name, DATE_FORMAT(Mon,'%H:%i') FROM `schedule07-21-2008` WHERE Mon >= '17:00' AND Mon <= '22:00';

I dont get any records returned. I wish i could see how DATE_FORMAT(Mon,'%H:%i') is looking since i can never tell how its formatted (if its even correct, that is).

david

[StealthRT]

Here is something close to what i am looking for...

I suppose I should have expanded my original answer, but to borrow lokus' fine examples (and add a couple more), let me belatedly explain why EndTime>'$FORM{StartTime}' AND StartTime<'$FORM{EndTime}' is the correct way to check for overlaps:

There are six different ways two date/times can array themselves relative to one another. In the following examples I'm going to change the equation to time1.endtime>time2.starttime AND time1.starttime<time2.endtime.

1) Overlapping to the left
time 1 |------------|
time 2 |------------|

time1.endtime > time2.starttime = true
time1.starttime < time2.endtime = true
Both conditions are true, the times overlap.

2) Overlapping to the right
time 1 |------------|
time 2 |------------|

time1.endtime > time2.starttime = true
time1.starttime < time2.endtime = true
Both conditions are true, the times overlap.

3) Overlapping in the middle
time 1 |------------|
time 2 |-------------------|

time1.endtime > time2.starttime = true
time1.starttime < time2.endtime = true
Both conditions are true, the times overlap.

4) Overlapping to the outside
time 1 |------------|
time 2 |--------|

time1.endtime > time2.starttime = true
time1.starttime < time2.endtime = true
Both conditions are true, the times overlap.

5) Not overlapping to the left
time 1 |------------|
time 2 |--------|

time1.endtime > time2.starttime = false
time1.starttime < time2.endtime = true
Both conditions are not true, the times do not overlap.

6) Not overlapping to the right
time 1 |------------|
time 2 |--------|

time1.endtime > time2.starttime = true
time1.starttime < time2.endtime = false
Both conditions are not true, the times do not overlap.

A grey area arises when considering "abutting" time intervals. In the equation above, 8:00-9:00 and 9:00-10:00 do NOT overlap (time1.endtime > time2.starttime fails because they are equal). If you want them to overlap, you need to fiddle with the < and > and make one or both <= or >= instead.

[kairog]

Hi

I'm not sure what you're getting at as I'm not a better English speaker but probably you can
use BETWEEN AND predicate

BETWEEN works in both numeric and dates

Perhaps you can change your data type to DATES
and then search through that field using the following sql command

SELECT Name, Mon FROM `schedule07-21-2008` where Mon between 20080727170000 and
20080727220000

Remember that the default timestamp stores dates in YYYYMMDDHHMMSS, so to get all entries for a single day, you need to start your range at midnight (00:00:00) and end it at 11:59:59 pm (23:59:59).

Hope that helps,
Jesse