Artful Common Queries

Find overlapping periods

from the Artful Common Queries page

You have a table of visits, and you wish to display the time periods during which there are visit time overlaps.

drop table if exists visits;
create table visits(id int primary key,start datetime,end datetime);
insert into visits values
(1, '2008-09-01 15:01', '2008-09-01 15:04'),
(2, '2008-09-01 15:02', '2008-09-01 15:09'),
(3, '2008-09-01 15:12', '2008-09-01 15:15'),
(4, '2008-09-01 16:11', '2008-09-01 16:23'),
(5, '2008-09-01 16:19', '2008-09-01 16:25'),
(6, '2008-09-01 17:52', '2008-09-01 17:59'),
(7, '2008-09-01 18:18', '2008-09-01 18:22'),
(8, '2008-09-01 16:20', '2008-09-01 16:22'),
(9, '2008-09-01 18:17', '2008-09-01 18:23');

There are five period overlaps in this data:

1     |-----|
2        |-----|
3                 |--|
4                       |-----|
5                          |-----|
6                                  |---|
7                                        |---| 
8                           |---|
9                                       |-----|

One solution is to use a View to identify starting and stopping events, then define an Overlaps View:

CREATE OR REPLACE VIEW events AS
SELECT start AS time, 1 AS value, id FROM visits
UNION
SELECT end AS time, -1 AS value, id FROM visits;

CREATE OR REPLACE VIEW overlaps AS
SELECT time t, (SELECT SUM(value) FROM events WHERE time <=t ) as visitcount
FROM events;

SELECT t, visitcount
FROM overlaps
WHERE visitcount>1
ORDER BY t;
+---------------------+------------+
| t                   | visitcount |
+---------------------+------------+
| 2008-09-01 15:02:00 |          2 |
| 2008-09-01 16:19:00 |          2 |
| 2008-09-01 16:20:00 |          3 |
| 2008-09-01 16:22:00 |          2 |
| 2008-09-01 18:18:00 |          2 |
+---------------------+------------+

But that doesn't show us when overlap periods end. There is a fuller and more straightforward solution: join visits to itself on the criteria that ...

(i) the first of each joined pair of visits started no earlier than the second,
(ii) the first visit started before the second ended, and
(iii) the second visit started before the first ended:

Thanks to Brian Moretta for pointing out the need for LEAST() to catch all endpoints:

SELECT v1.id, v1.start, v2.id, LEAST(v1.end,v2.end) AS end
FROM visits v1
JOIN visits v2 ON v1.id <> v2.id and v1.start >= v2.start and v1.start < v2.end 
ORDER BY v1.start;
+----+---------------------+----+---------------------+
| id | start               | id | end                 |
+----+---------------------+----+---------------------+
|  2 | 2008-09-01 15:02:00 |  1 | 2008-09-01 15:04:00 |
|  5 | 2008-09-01 16:19:00 |  4 | 2008-09-01 16:23:00 |
|  8 | 2008-09-01 16:20:00 |  4 | 2008-09-01 16:22:00 |
|  8 | 2008-09-01 16:20:00 |  5 | 2008-09-01 16:22:00 |
|  7 | 2008-09-01 18:18:00 |  9 | 2008-09-01 18:22:00 |
+----+---------------------+----+---------------------+

Last updated 16 Aug 2019