MySQL 5.7

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table slots (
	start datetime,
  	end datetime
);
insert into slots values ('2022-09-22 08:00','2022-09-22 10:00');
insert into slots values ('2022-09-22 09:00','2022-09-22 12:00');
insert into slots values ('2022-09-22 08:00','2022-09-22 12:00');
insert into slots values ('2022-09-22 11:00','2022-09-22 13:00');
insert into slots values ('2022-09-22 7:00','2022-09-22 08:30');
insert into slots values ('2022-09-22 09:00','2022-09-22 11:00');
SELECT * FROM slots;


start
end


2022-09-22 08:00:00
2022-09-22 10:00:00

2022-09-22 09:00:00
2022-09-22 12:00:00

2022-09-22 08:00:00
2022-09-22 12:00:00

2022-09-22 11:00:00
2022-09-22 13:00:00

2022-09-22 07:00:00
2022-09-22 08:30:00

2022-09-22 09:00:00
2022-09-22 11:00:00


-- timepoints list
SELECT start dt FROM slots
UNION DISTINCT
SELECT `end` FROM slots


dt


2022-09-22 08:00:00

2022-09-22 09:00:00

2022-09-22 11:00:00

2022-09-22 07:00:00

2022-09-22 10:00:00

2022-09-22 12:00:00

2022-09-22 13:00:00

2022-09-22 08:30:00


-- timepoints list with separate ranges amount change
SELECT start dt, 1 weight FROM slots
UNION ALL
SELECT `end`, -1 FROM slots
ORDER BY 1 ASC, 2 DESC


dt
weight


2022-09-22 07:00:00
1

2022-09-22 08:00:00
1

2022-09-22 08:00:00
1

2022-09-22 08:30:00
-1

2022-09-22 09:00:00
1

2022-09-22 09:00:00
1

2022-09-22 10:00:00
-1

2022-09-22 11:00:00
1

2022-09-22 11:00:00
-1

2022-09-22 12:00:00
-1

2022-09-22 12:00:00
-1

2022-09-22 13:00:00
-1


-- timepoints list with total ranges amount change
SELECT dt, SUM(weight) weight
FROM (
  SELECT start dt, 1 weight FROM slots
  UNION ALL
  SELECT `end`, -1 FROM slots
  ) ponts
GROUP BY 1


dt
weight


2022-09-22 07:00:00
1

2022-09-22 08:00:00
2

2022-09-22 08:30:00
-1

2022-09-22 09:00:00
2

2022-09-22 10:00:00
-1

2022-09-22 11:00:00
0

2022-09-22 12:00:00
-2

2022-09-22 13:00:00
-1


-- the amount of ranges immediately after the timepoint
SELECT points1.dt, SUM(points2.weight) weight
FROM (
  SELECT dt, SUM(weight) weight
  FROM (
    SELECT start dt, 1 weight FROM slots
    UNION ALL
    SELECT `end`, -1 FROM slots
    ) ponts
  GROUP BY 1
  ) points1
JOIN (
  SELECT dt, SUM(weight) weight
  FROM (
    SELECT start dt, 1 weight FROM slots
    UNION ALL
    SELECT `end`, -1 FROM slots
    ) ponts
  GROUP BY 1
  ) points2 ON points1.dt >= points2.dt
GROUP BY 1
ORDER BY 1


dt
weight


2022-09-22 07:00:00
1

2022-09-22 08:00:00
3

2022-09-22 08:30:00
2

2022-09-22 09:00:00
4

2022-09-22 10:00:00
3

2022-09-22 11:00:00
3

2022-09-22 12:00:00
1

2022-09-22 13:00:00
0


-- maximal amount of overlapped ranges
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


max_weight


4

start	end
2022-09-22 08:00:00	2022-09-22 10:00:00
2022-09-22 09:00:00	2022-09-22 12:00:00
2022-09-22 08:00:00	2022-09-22 12:00:00
2022-09-22 11:00:00	2022-09-22 13:00:00
2022-09-22 07:00:00	2022-09-22 08:30:00
2022-09-22 09:00:00	2022-09-22 11:00:00