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, '2024-09-01 15:01', '2024-09-01 15:04'), (2, '2024-09-01 15:02', '2024-09-01 15:09'), (3, '2024-09-01 15:12', '2024-09-01 15:15'), (4, '2024-09-01 16:11', '2024-09-01 16:23'), (5, '2024-09-01 16:19', '2024-09-01 16:25'), (6, '2024-09-01 17:52', '2024-09-01 17:59'), (7, '2024-09-01 18:18', '2024-09-01 18:22'), (8, '2024-09-01 16:20', '2024-09-01 16:22'), (9, '2024-09-01 18:17', '2024-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 | +---------------------+------------+ | 2024-09-01 15:02:00 | 2 | | 2024-09-01 16:19:00 | 2 | | 2024-09-01 16:20:00 | 3 | | 2024-09-01 16:22:00 | 2 | | 2024-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 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 | 2024-09-01 15:02:00 | 1 | 2024-09-01 15:04:00 | | 5 | 2024-09-01 16:19:00 | 4 | 2024-09-01 16:23:00 | | 8 | 2024-09-01 16:20:00 | 4 | 2024-09-01 16:22:00 | | 8 | 2024-09-01 16:20:00 | 5 | 2024-09-01 16:22:00 | | 7 | 2024-09-01 18:18:00 | 9 | 2024-09-01 18:22:00 | +----+---------------------+----+---------------------+ |