How do you query between two ranges of time without including the two ranges themselves?

I have a working code of querying time using whereBetween but I'm having an issue because I only want to get the records between those two ranges of time, not including themselves.

I have these rows on my schedules table:

 ---------- ------------------ ---------------- ------------- 
|    id    |    start_time    |    end_time    |   user_id   |
 ---------- ------------------ ---------------- ------------- 
|    1     |     06:45:00     |    07:45:00    |      1      |
|    2     |     10:30:00     |    11:30:00    |      1      |
 ---------- ------------------ ---------------- ------------- 

Steps I'm doing:

1. Add a new record to schedules table.
2. Let's say my new record contains start_time of 07:45:00 and end_time of 08:45:00.
3. Check if any record is in between the record to be added, using the code below.

The code I use to check if there is a row that in between the $startTime and $endTime.

$userId = 1;
$startTime = Carbon::parse('07:45')->format('H:i:s');
$endTime = Carbon::parse('08:45')->format('H:i:s');

$checkScheduleIfExists = Schedule::where('user_id', $userId)
    ->where(function ($query) use ($startTime, $endTime) {
        $query->whereBetween('start_time', [
            $startTime, $endTime
        ])
        ->orWhereBetween('end_time', [
            $startTime, $endTime
        ]);
    })
    ->first();

dd($checkScheduleIfExists);

// this returns the row id 1, 06:45:00 - 07:45:00

The problem with the code above is it finds the $startTime in the schedules table's end_time which is the 07:45:00 and it returns that row, same goes if it finds the $endTime in start_time.

What I expect is only to return a row (to know a record exists) if the $startTime (07:45:00) occupied lower than theend_time that exists in my schedules table, for example, 7:40:00.

CodePudding user response：

This is a pretty standard problem. And the short answer is: given an interval which starts at S and ends at E, the DB query to get overlapping intervals should look like:

where start<E and end>S

Explanantion:

To visualize (and better remember) this, imagine two intervals which DON'T overlap. Let's call them S1-E1 and S2-E2.

If they don't overlap, that means that one of them comes first, and the other one comes second. We don't know which one is which, so either E1 <= S2 (the first one ends before the second one starts) or E2 <= S1 (the second one ends before the first one starts).

And now that we've written that down, it becomes easy to get all the cases where the two intervals DO overlap in some way - just slap a not (negation) in front of this condition. So, in formula-speak: not(E1 <= S2 or E2 <= S1). This condition takes into account every possible way how the intervals could possibly overlap, including one interval completely including the other interval, or intervals overlapping partially.

We can further simplify this expression by opening the parenthesis, in which case we get E1 > S2 and E2 > S1.