CODEWITHSUNDEEP
mysqlsqldatetimerangeoverlap
fudo
fudo

Reputation: 2880

Determine max number of overlapping DATETIME ranges

I got a table with some DATETIME ranges, something like

id | start               | end
----------------------------------------------
1  | 2011-12-18 16:00:00 | 2011-12-18 17:00:00
2  | 2011-12-19 08:00:00 | 2011-12-19 10:00:00
3  | 2011-12-19 11:00:00 | 2011-12-19 13:00:00
4  | 2011-12-19 12:00:00 | 2011-12-19 14:00:00
5  | 2011-12-19 13:00:00 | 2011-12-19 15:00:00
6  | 2011-12-19 13:00:00 | 2011-12-19 14:00:00
7  | 2011-12-20 13:00:00 | 2011-12-20 14:00:00

So for day 2011-12-19 the ranges spans like this:

8    9   10   11   12   13   14   15
<-------->
               <-------->
                    <-------->
                         <-------->
                         <---->

The goal is, when inserting new record, to find the max number of overlapping ranges already present: i.e.: when inserting the new range 2011-12-19 12:00:00 - 2011-12-19 15:00:00 i would like to receive 3, because the max number of overlapping ranges is 3, from 13:00 to 14:00.

Since now i managed to have this

select
    count(*) as cnt
from
    mytable as p
where
    ( # check if new renge overlap existings ones
        (@start >= start and @start < end)
        or
        (@end > start and @end <= end)
    )
    or
    ( # check if existing range is included by new one
        start between @start and @end
        and
        end between @start and @end
    )

But this return 4 because it detects all ranges except the first, but is wrong.

I already found

But all these questions are slightly different.

I'm on MysQL 5.7, but upgrading to 8 is possibile if necessary.

Upvotes: 4

Views: 2007

Answers (2)

Radim Bača
Radim Bača

Reputation: 10701

This answer is for MySQL 8.0 that contains window functions. The core of the solution will be the following query that finds a number of overlapping intervals for every interesting interval in the data:

select t2.startDt, t2.endDt, count(*) overlaps_count
from
(
    select lag(t1.dt) over (order by t1.dt) startDt, t1.dt endDt
    from
    (
        select startt dt from data
        union
        select endt dt from data
    ) t1
) t2
join data on t2.startDt < data.endt and t2.endDt > data.startt
group by t2.startDt, t2.endDt

DBFiddle DEMO

Once you have this result (let call it Overlap table) then you may easily find the maximum for an input interval as follows

with Overlap as
(
   -- the query above
)
select max(overlaps_count)
from Overlap 
where @start < endDt and @end > startDt

Upvotes: 6

Cetin Basoz
Cetin Basoz

Reputation: 23797

Considering start and end would never be same:

select
    count(*) as cnt
from
    mytable as p
where
    # check if new renge overlap existings ones
        (@start < end and @end > start);

Upvotes: -1

Related Questions