Check overlap of date ranges in MySQL

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?

Contents hide

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 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)):

ns – ne – es – ee: doesn’t overlap and doesn’t match (because ne < es)
ns – es – ne – ee: overlaps and matches
es – ns – ee – ne: overlaps and matches
es – ee – ns – ne: doesn’t overlap and doesn’t match (because ns > ee)
es – ns – ne – ee: overlaps and matches
ns – es – ee – ne: overlaps and matches

Here is a fiddle

Method 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.

Method 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.

Method 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.

Method 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.

Method 6

Recently I was struggling with the same issue and came to end with this one easy step (This may not be a good approach or memory consuming)-

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 the entries with overlapping date ranges.

Hope this helps someone.

Method 7

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.

Method 8

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;

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating