您可以使用递归cte从可用性列表中构建时隙间隔，然后将阻塞的可用性left join到cte。然后，可以对在被阻止的可用性中不具有匹配的行执行排名（因此它是可用的），并且可以对排名值进行分组以找到每个间隔的总分钟数：

with recursive cte(r, day, min, end) as (
   select row_number() over (order by a.day), a.day, a.start, a.end from availability a
   union all
   select c.r, c.day, c.min + interval 1 minute, c.end from cte c where c.min < c.end
),
slots as (
   select c.day, c.r, c.min, c.end, a.start is null av from cte c 
   left join blocked_availability a on c.day = a.day and a.start < c.min and c.min < a.end
   order by c.end
),
a_slots as (
   select (select sum(s1.day = s.day and s1.min < s.min and s1.av != s.av) from slots s1) r1, s.r, s.day, s.min, s.av from slots s
)
select t.m1, t.m2 from (select a.r1, a.r, min(a.min) m1, max(a.min) m2 
    from a_slots a where a.av group by a.r1, a.r) t
where time_to_sec(timediff(t.m2, t.m1)) / 60 >= 60 -- here, specifying the number of minutes the interval must be

See fiddle

从两个表中取消透视值（这里使用union all完成），然后使用lead（）。这将从表中生成所有可能的期间。现在，您可以连接原始数据，过滤“阻塞”时段并计算时间差。

with t(day, t1, t2) as (
  select day, t1, lead(t1) over (order by t1) t2 
  from (
    select day, start t1 from availability union all
    select day, end   from availability union all
    select day, start from blocked_availability union all
    select day, end   from blocked_availability ) upvt )
select t.day, t.t1, t.t2, 
       timediff(str_to_date(t.t2, '%H:%i'), str_to_date(t.t1, '%H:%i')) diff 
from t
join availability a 
  on a.day = t.day and t.t1 < a.end and a.start < t.t2
left join blocked_availability b 
  on b.day = t.day and t.t1 < b.end and b.start < t.t2
where b.day is null 
order by t.day, t.t1

dbfiddle demo