Overlapping time intervals

Question

Giving the following table, I would like to discover the time interval where an overlap occurs between 2 or more users, and then record those times along with the users involved in the overlap. I have no clue of how I should approach this, so any help will be appreciated

Table1:

UserID  StartDate   EndDate
1       15:00       22:00
2       12:00       18:00
3       02:00       09:00
4       07:00       13:00

Expected Result:

StartDate       EndDate     Users
15:00           18:00       1,2
07:00           09:00       3,4
12:00           13:00       2,4

These may be useful: [6571538/checking-a-table-for-time-overlap](http://stackoverflow.com/questions/6571538/checking-a-table-for-time-overlap). Also: [flipflops.org/2008/05/01/finding-overlapping-dates-and-times-in-mysql](http://www.flipflops.org/2008/05/01/finding-overlapping-dates-and-times-in-mysql/) — Ryan Vincent, Jun 16 '14 at 13:34

score 4 · Accepted Answer · answered Jun 16 '14 at 13:39

Here it is in SQL Fiddle.

But if you want some explanation:

I joined the table twice on those rows that has overlapping:

from over t1 join over t2 on t1.START < t2.END and t1.START > t2.START

then selected simply the highest start and lowest end dates:
```
greatest(t1.START, t2.START), least(t1.END, t2.END)
```
... and in a nested select grouped the ids of those rows that covers the iterval:
```
t3.START <= greatest(t1.START, t2.START) and t3.END >= least(t1.END, t2.END)
```

Overlapping time intervals

1 Answers1