Check overlap of date ranges 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 don't want to have conflict between 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 session(s).

Here is my query:

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 that?

---

fiddle

Furlong

Your third condition is wrong. It is supposed to be "2010-01-05" <= start_date AND "2010-01-25" >= end_date. See https://mcmap.net/q/218913/-mysql-query-for-certain-date-range for visualization. Your current third condition will never evaluate, because the first (and second) condition already covers it. – Eleusis

Answer 1

I had such a query with a calendar application 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)):

1. ns - ne - es - ee: doesn't overlap and doesn't match (because ne < es)
2. ns - es - ne - ee: overlaps and matches
3. es - ns - ee - ne: overlaps and matches
4. es - ee - ns - ne: doesn't overlap and doesn't match (because ns > ee)
5. es - ns - ne - ee: overlaps and matches
6. ns - es - ee - ne: overlaps and matches

---

Here is a fiddle

Puppis

Works great!, but I think @Pierre de LESPINAY is looking for inclusive ranges in his query: WHERE new_start <= existing_end AND new_end >= existing_start; – Moua

@OsvaldoM. If he really were, he would have complained about 2 years ago … – Puppis

I an event end today and another starts today, do they overlap ? In my opinion, they will overlap. But the SQL query won't say it: sqlfiddle.com/#!2/0a6fd/1/0 – Priscella

@soulmerge, Actually, he would have just added the = into his actual code instead of bothering to complain about it. – Eleusis

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 clauses cover the 4 cases of overlapping required.

Denisse

Even if the OP was not apparently looking for this overlapping definition, this answer is the best solution for the problem described by the question name. I was looking for this overlap, which is the true overlap. – Sanity

I'm not sure what you mean by "true overlap" but the top answer by @Puppis is equivalent to this answer by Lamy. I was able to use the Z3 Theorem Solver to prove equivalence: grantjenks.com/projects/equivalent-inequalities – Eldon

Answer 3

Lamy's answer is good, but you can optimize it a little more.

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 the two where they don't.

Farny

Are there other solutions besides this and the other two above? – Eleusis

Answer 4

I had faced the similar problem. My problem was to stop booking between a range of blocked dates. For example booking is blocked for a property between 2nd may to 7th may. I needed to find any kind of overlapping date to detect and stop the booking. 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 it doesn't work.

Durfee

Answer 5

Given two intervals like (s1, e1) and (s2, e2) with s1<e1 and s2<e2
You can calculate overlapping like this:

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 

Will also work if one interval is within the other one.

Charin

Answer 6

You can cover all date overlapping cases even when to-date in database can possibly be null as follows:

SELECT * FROM `tableName` t
WHERE t.`startDate` <= $toDate
AND (t.`endDate` IS NULL OR t.`endDate` >= $startDate);

This will return all records that overlaps with the new start/end dates in anyway.

Vamoose

Answer 7

Mackraken's answer above is better from a performance perspective as it doesn't require several OR's in order to evaluate if two dates overlap. Nice solution!

However I found that in MySQL you need to use DATEDIFF instead of the minus operator -

SELECT o.orderStart, o.orderEnd, s.startDate, s.endDate
, GREATEST(LEAST(orderEnd, endDate) - GREATEST(orderStart, startDate), 0)>0 as overlaps
, DATEDIFF(LEAST(orderEnd, endDate), GREATEST(orderStart, startDate)) as overlap_length
FROM orders o
JOIN dates s USING (customerId)
WHERE 1
AND DATEDIFF(LEAST(orderEnd, endDate),GREATEST(orderStart, startDate)) > 0;

Neptunium

Answer 8

Recently I was struggling to perform operations with date. The following SQL query solved the issue to filter rows with multiple criteria related to date:

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)
);

PS: This helped me find the entries with overlapping date ranges however may not be a good approach as it may be memory consuming.

Jumble