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

Whats the most efficient way to perform such a query in mysql?

Thx very much Martin

Answer 1

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.

Answer 2

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)

Answer 3

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.