MariaDB 10.4

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

select version();


version()


10.4.26-MariaDB-1:10.4.26+maria~deb10-log


create table date_table (id int, started date, stop date);
insert into date_table
values 
(1, date '2017-08-14', date '2017-10-22'),
(2, date '2017-10-23', date '2017-12-12'),
(3, date '2017-12-13', date '2019-02-21'),
(4, date '2022-01-01', null),
(5, date '2020-09-08', date '2020-09-14'),
(6, date '2020-09-15', date '2020-10-14'),
(7, date '2019-02-22', date '2019-03-18'),
(8, date '2022-08-01', date '2022-08-02'),
(9, date '2022-08-03', date '2022-08-04'),
(10, date '2022-08-06', date '2022-08-07'),
(11, date '2022-08-08', null);

Records: 11  Duplicates: 0  Warnings: 0


select started, stop, count(flag) over (order by started) as grp
from
(
  select 
    started, stop,
    case when lag(stop) over (order by started) = started - interval 1 day then null else 1 end as flag
  from date_table
) with_flags
order by started;


started
stop
grp


2017-08-14
2017-10-22
1

2017-10-23
2017-12-12
1

2017-12-13
2019-02-21
1

2019-02-22
2019-03-18
1

2020-09-08
2020-09-14
2

2020-09-15
2020-10-14
2

2022-01-01
null
3

2022-08-01
2022-08-02
4

2022-08-03
2022-08-04
4

2022-08-06
2022-08-07
5

2022-08-08
null
5


select
  grp,
  min(started) as started,
  case when count(*) = count(stop) then max(stop) end as stop
from
(
  select started, stop, count(flag) over (order by started) as grp
  from
  (
    select 
      started, stop,
      case when lag(stop) over (order by started) = started - interval 1 day then null else 1 end as flag
    from date_table
  ) with_flags
) grouped
group by grp
order by grp;


grp
started
stop


1
2017-08-14
2019-03-18

2
2020-09-08
2020-10-14

3
2022-01-01
null

4
2022-08-01
2022-08-04

5
2022-08-06
null

started	stop	grp
2017-08-14	2017-10-22	1
2017-10-23	2017-12-12	1
2017-12-13	2019-02-21	1
2019-02-22	2019-03-18	1
2020-09-08	2020-09-14	2
2020-09-15	2020-10-14	2
2022-01-01	null	3
2022-08-01	2022-08-02	4
2022-08-03	2022-08-04	4
2022-08-06	2022-08-07	5
2022-08-08	null	5