How to find maximum time range collision occurencies in Mysql

Question

I have a time range entity with start and end datetime column. I need to find the maximum occurrencies (count) of overlapping the same time slot.

In the example above, the count is 4.

https://www.db-fiddle.com/f/pcq1MjQeqSEMDdyGxkFsR5/0

Probably I need some recurring query but I don't know how to start.

Does your practical MySQL version is 5.x? – Akina Sep 22 '22 at 07:32 — Akina, Sep 22 '22 at 07:32

score 2 · Accepted Answer · answered Sep 22 '22 at 07:46

For MySQL 5.x:

SELECT SUM(points2.weight) max_weight
FROM (
  SELECT start dt FROM slots
  UNION DISTINCT
  SELECT `end` FROM slots
  ) points1
JOIN (
  SELECT dt, SUM(weight) weight
  FROM (
    SELECT start dt, 1 weight FROM slots
    UNION ALL
    SELECT `end`, -1 FROM slots
    ) points
  GROUP BY dt
  ) points2 ON points1.dt >= points2.dt
GROUP BY points1.dt
ORDER BY max_weight DESC LIMIT 1

https://dbfiddle.uk/f0b56Q4X (step-by-step, with comments)

I don't understand it, but it works :-) many thanks. I'll try to study it again — Tobia, Sep 23 '22 at 13:18

How to find maximum time range collision occurencies in Mysql

1 Answers1

Linked