MySQL查询某表中与另一表时间间隔超指定时长的记录

需求说明 #

筛选tableA中与tableB里任意时间间隔≥2小时的所有记录，可忽略夏令时转换、时区及跨午夜日期衔接问题。

样本表 #

tableA

times
00:00:00
00:30:00
01:00:00
01:30:00
02:00:00
02:30:00
03:00:00
03:30:00
04:00:00
04:30:00
.... 当日其他30分钟间隔的时间
22:00:00
22:30:00
23:00:00
23:30:00

tableB

11:00:00
15:30:00

期望结果 #

00:00:00
to
09:00:00
13:00:00
13:30:00
17:30:00
to
23:30:00

可行查询实现 #

以下是验证可用的查询语句，通过NOT EXISTS子句判断时间间隔是否符合要求，同时结合业务场景增加了日程时间范围、已占用时间、休息时段的过滤逻辑：

set @dayToCheck = '2024-04-01';
set @earliest = '09:00:00';
set @latest = '17:00:00';
set @breakStart = '00:00:00';
set @breakDuration = 0;
set @inc = 59;


select t.times from times t 

# 排除已被占用的时间
where t.times not in (select a.time from aptSchedule a where a.date = @dayToCheck)

# 限定在常规工作时段内
and t.times >= @earliest
and t.times <= @latest

# 排除休息时段
and t.times not in (select s.times from times s where s.times >= @breakStart and times < DATE_ADD(concat(@dayToCheck,' ', @breakStart), INTERVAL @breakDuration HOUR))

# 确保与所有已预约时间间隔超出指定时长（此处为59分钟）
and not exists 
    (select 1 from 
        (select time from aptSchedule b where date = @dayToCheck) dr
    where abs(time_to_sec(timediff(t.times,dr.time))) <= @inc*60);

内容的提问来源于stack exchange，提问作者Altimus Prime