Find overlapping (date/time) rows within one table

Question

I have a table which stores in each row a meeting with start date/time and end date/time.

meetingID int
meetingStart datetime
meetingEnd datetime

Desired output: For each pair of overlapping rows I would like to output
meetingID, meetingStart, meetingID, meetingEnd

What's the most efficient way to perform such a query in MySQL?

explanation: [Comparing date ranges](http://stackoverflow.com/questions/143552/comparing-date-ranges) — viral, Mar 28 '16 at 11:50

Quassnoi · Accepted Answer · 2009-03-27T12:01:26.800

SELECT  m1.meetingID, m1.meetingStart, m1.meetingEnd, m2.meetingID
FROM    t_meeting m1, t_meeting m2
WHERE   (m2.meetingStart BETWEEN m1.meetingStart AND m1.meetingEnd
        OR m2.meetingEnd BETWEEN m1.meetingStart AND m1.meetingEnd)
        AND m1.meetingID <> m2.meetingID

This will select each pair twice.

If you want each pair to be selected just once, use:

SELECT  m1.meetingID, m1.meetingStart, m1.meetingEnd, m2.meetingID
FROM    t_meeting m1, t_meeting m2
WHERE   (m2.meetingStart BETWEEN m1.meetingStart AND m1.meetingEnd
        OR m2.meetingEnd BETWEEN m1.meetingStart AND m1.meetingEnd)
        AND m2.meetingID > m1.meetingID

Make sure you have indexes on meetingStart and meetingEnd for the query to work efficiently.

MySQL, however, will probably use INDEX MERGE to run this query, which is not very efficient in current implementation.

You also may try to use:

SELECT  m1.*, m2.*
FROM    (
        SELECT  m1.meetingID AS mid1, m2.meetingID AS mid2
        FROM    t_meeting m1, t_meeting m2
        WHERE   m2.meetingStart BETWEEN m1.meetingStart AND m1.meetingEnd
                AND m2.meetingID <> m1.meetingID
        UNION
        SELECT  m1.meetingID, m2.meetingID
        FROM    t_meeting m1, t_meeting m2
        WHERE   m2.meetingEnd BETWEEN m1.meetingStart AND m1.meetingEnd
                AND m2.meetingID <> m1.meetingID
        ) mo, t_meeting m1, t_meeting m2
WHERE   m1.meetingID = mid1
        AND m2.meetingID = mid2

, which is more complex but will most probably run a little bit faster.

and to the last query, maybe add "AND m2.meetingID > m1.meetingID" to prevent doubles. — miccet, Mar 27 '09 at 12:14
This works, but in the context of meetings, you may get unwanted results. For example, if you have meetings 5pm-6pm, and 6pm-7pm, it matches since they both have 6pm — Señor Reginold Francis, Oct 16 '15 at 15:23
@Señor Reginold Francis and how to make them not match on 6pm? we need to add +- 1 minute, right? — Oleg Abrazhaev, Jun 12 '17 at 10:05

score 2 · Answer 2 · answered Oct 16 '15 at 15:40

Try using this query. It is Quassnoi's solution modified to ignore cases where the end of one booking is the same as the start of another.

SELECT  m1.meetingID_id, m1.meetingStart , m1.meetingEnd, m2.meetingID_id
FROM    bookings m1, bookings m2
WHERE   (m2.meetingStart BETWEEN m1.start AND DATE_SUB(m1.meetingEnd, INTERVAL 1 second)
        OR DATE_SUB(m2.meetingEnd, INTERVAL 1 second) BETWEEN m1.meetingStart AND m1.end)
        AND m1.meetingID_id > m2.meetingID_id

score 0 · Answer 3 · answered Mar 27 '09 at 11:59

Probably something like this:

SELECT m1.meetingID, m2.meetingID
FROM meeting AS m1, meeting AS m2
WHERE m1.meetingID < m2.meetingID
    AND m1.meetingStart BETWEEN m2.meetingStart AND m2.meetingEnd
    OR m1.meetingEnd BETWEEN m2.meetingStart AND m2.meetingEnd

By selecting only m1.meetingID < m2.meetingID you dont compare rows with itself and don't get duplicates because every row would be joined twice (m1, m2) and (m2, m1)

score 0 · Answer 4 · answered Mar 27 '09 at 12:53

Adding both meetings' start and end times to the result rows:

SELECT m1.meetingID AS firstID, m1.meetingStart AS firstStart, 
m1.meetingEnd AS firstEnd, m2.meetingID AS secondID, 
m2.meetingStart AS secondStart, m2.meetingEnd AS secondEnd 
FROM meeting AS m1, meeting AS m2 
WHERE (m2.meetingStart BETWEEN m1.meetingStart AND m1.meetingEnd) 
AND (m1.meetingID != m2.meetingID)

This way m2 will always be the one starting at the same time or after m1, and m1.id!=m2.id ensures that it will not contain matches against itself.

You don't need to check against the meeting end, as the overlap can be reliably detected from just comparing the meeting start.

Find overlapping (date/time) rows within one table

4 Answers4

Linked