Check date ranges match in MySQL

Question

This table is used to store sessions (events):

CREATE TABLE session ( id int(11) NOT NULL AUTO_INCREMENT , start_date date , end_date date ); INSERT INTO session (start_date, end_date) VALUES ("2010-01-01", "2010-01-10") , ("2010-01-20", "2010-01-30") , ("2010-02-01", "2010-02-15") ; 

We do not want to have a conflict between the ranges.
Let's say we need to insert a new session from 2010-01-05 to 2010-01-25 .
We would like to know the conflicting sessions.

Here is my request:

 SELECT * FROM session WHERE "2010-01-05" BETWEEN start_date AND end_date OR "2010-01-25" BETWEEN start_date AND end_date OR "2010-01-05" >= start_date AND "2010-01-25" <= end_date ; 

Here is the result:

 +----+------------+------------+ | id | start_date | end_date | +----+------------+------------+ | 1 | 2010-01-01 | 2010-01-10 | | 2 | 2010-01-20 | 2010-01-30 | +----+------------+------------+ 

Is there a better way to get this?

---

fiddle

Answer 1

I had such a request with a calendar application that I once wrote. I think I used something like this:

 ... WHERE new_start < existing_end AND new_end > existing_start; 

UPDATE This should definitely work ((ns, ne, es, ee) = (new_start, new_end, existing_start, existing_end)):

• ns - ne - es - ee: does not overlap and does not match (because ne <es)
• ns - es - ne - ee: matches and matches
• es - ns - ee - ne: matches and matches
• es - ee - ns - ne: does not overlap and does not match (since ns> ee)
• es - ns - ne - ee: matches and matches
• ns - es - ee - ne: matches and matches

---

Here is the fiddle

Answer 2

 SELECT * FROM tbl WHERE existing_start BETWEEN $newStart AND $newEnd OR existing_end BETWEEN $newStart AND $newEnd OR $newStart BETWEEN existing_start AND existing_end if (!empty($result)) throw new Exception('We have overlapping') 

These 3 lines of sql sentences cover 4 cases of overlap.

Answer 3

The answer to Lamy is good, but you can optimize it a bit.

 SELECT * FROM tbl WHERE existing_start BETWEEN $newSTart AND $newEnd OR $newStart BETWEEN existing_start AND existing_end 

This will catch all four scenarios where the ranges overlap and exclude two where they are not.

Answer 4

I ran into a similar problem. My problem was to stop the reservation between a range of blocked dates. For example, a reservation is blocked for a property from May 2 to May 7. I needed to find some overlapping date for finding and stopping the reservation. My solution is similar to LordJavac.

 SELECT * FROM ib_master_blocked_dates WHERE venue_id=$venue_id AND ( (mbd_from_date BETWEEN '$from_date' AND '$to_date') OR (mbd_to_date BETWEEN '$from_date' AND '$to_date') OR ('$from_date' BETWEEN mbd_from_date AND mbd_to_date) OR ('$to_date' BETWEEN mbd_from_date AND mbd_to_date) ) *mbd=master_blocked_dates 

Let me know if this does not work.

Answer 5

For two intervals, such as (s1, e1) and (s2, e2) with s1 <e1 and s2 & lt2 e2, you can calculate the overlap as follows:

 SELECT s1, e1, s2, e2, ABS(e1-s1) as len1, ABS(e2-s2) as len2, GREATEST(LEAST(e1, e2) - GREATEST(s1, s2), 0)>0 as overlaps, GREATEST(LEAST(e1, e2) - GREATEST(s1, s2), 0) as overlap_length FROM test_intervals 

It will also work if one interval is inside another.

Answer 6

I recently struggled with the same problem and completed this simple step (this may not be a good approach or memory consumption) -

 SELECT * FROM duty_register WHERE employee = '2' AND ( ( duty_start_date BETWEEN {$start_date} AND {$end_date} OR duty_end_date BETWEEN {$start_date} AND {$end_date} ) OR ( {$start_date} BETWEEN duty_start_date AND duty_end_date OR {$end_date} BETWEEN duty_start_date AND duty_end_date) ); 

This helped me find records with overlapping date ranges.

Hope this helps someone.