MySQL 8.0

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

create table t (
  id int not null primary key auto_increment,
  start_dt datetime,
  end_dt datetime
)
insert into t (start_dt, end_dt) values
('2023-06-01 06:00:00', '2023-06-01 18:00:00'),
('2023-06-02 06:00:00', '2023-06-02 18:00:00'),
('2023-06-03 06:00:00', '2023-06-03 18:00:00'),
('2023-06-04 06:00:00', '2023-06-04 18:00:00')

Records: 4  Duplicates: 0  Warnings: 0


set @dt1 = '2023-06-01 12:00:00';
set @dt2 = '2023-06-04 12:00:00';
with cte1(dt, type) as (
    select start_dt, 'e' from t where @dt2 > start_dt and end_dt > @dt1
    union all
    select end_dt, 's' from t where @dt2 > start_dt and end_dt > @dt1
    union all
    select @dt1, 's'
    union all
    select @dt2, 'e'
), cte2(dt1, dt2) as (
    select dt, case when type = 's' and lead(type) over (order by dt) = 'e' then lead(dt) over (order by dt) end
    from cte1
)
select *
from cte2
where dt2 is not null
order by dt1


dt1
dt2


2023-06-01 18:00:00
2023-06-02 06:00:00

2023-06-02 18:00:00
2023-06-03 06:00:00

2023-06-03 18:00:00
2023-06-04 06:00:00


set @dt1 = '2023-06-01 03:00:00';
set @dt2 = '2023-06-04 21:00:00';
with cte1(dt, type) as (
    select start_dt, 'e' from t where @dt2 > start_dt and end_dt > @dt1
    union all
    select end_dt, 's' from t where @dt2 > start_dt and end_dt > @dt1
    union all
    select @dt1, 's'
    union all
    select @dt2, 'e'
), cte2(dt1, dt2) as (
    select dt, case when type = 's' and lead(type) over (order by dt) = 'e' then lead(dt) over (order by dt) end
    from cte1
)
select *
from cte2
where dt2 is not null
order by dt1


dt1
dt2


2023-06-01 03:00:00
2023-06-01 06:00:00

2023-06-01 18:00:00
2023-06-02 06:00:00

2023-06-02 18:00:00
2023-06-03 06:00:00

2023-06-03 18:00:00
2023-06-04 06:00:00

2023-06-04 18:00:00
2023-06-04 21:00:00

dt1	dt2
2023-06-01 18:00:00	2023-06-02 06:00:00
2023-06-02 18:00:00	2023-06-03 06:00:00
2023-06-03 18:00:00	2023-06-04 06:00:00

dt1	dt2
2023-06-01 03:00:00	2023-06-01 06:00:00
2023-06-01 18:00:00	2023-06-02 06:00:00
2023-06-02 18:00:00	2023-06-03 06:00:00
2023-06-03 18:00:00	2023-06-04 06:00:00
2023-06-04 18:00:00	2023-06-04 21:00:00