查询指定时间区间内及重叠的时间间隔，SQL语句是否存在遗漏？

数据表定义 #

CREATE TABLE time_interval (
    id SERIAL PRIMARY KEY,
    start_time TIMESTAMP NOT NULL,
    end_time TIMESTAMP
);

样例数据 #

INSERT INTO time_interval (start_time, end_time) VALUES
    ('2024-02-10 01:30:00',                  null), -- pending    
    ('2024-02-10 03:00:00',                  null), -- pending
    ('2024-02-10 07:00:00', '2024-02-10 10:30:00'),
    ('2024-02-10 09:00:00', '2024-02-10 12:00:00'),
    ('2024-02-10 11:30:00', '2024-02-10 15:00:00'),
    ('2024-02-10 13:30:00', '2024-02-10 17:30:00'),
    ('2024-02-10 16:00:00', '2024-02-10 20:00:00'),
    ('2024-02-10 18:30:00', '2024-02-10 22:00:00'),
    ('2024-02-10 21:00:00', '2024-02-10 23:30:00');

查询需求 #

查询所有处于2024-02-10 10:00:00 - 2024-02-10 17:00:00区间内及与该区间重叠的时间间隔。

预期结果 #

('2024-02-10 01:30:00',                  null)
('2024-02-10 03:00:00',                  null)
('2024-02-10 07:00:00', '2024-02-10 10:30:00')
('2024-02-10 09:00:00', '2024-02-10 12:00:00')
('2024-02-10 11:30:00', '2024-02-10 15:00:00')
('2024-02-10 13:30:00', '2024-02-10 17:30:00')
('2024-02-10 16:00:00', '2024-02-10 20:00:00')

用户尝试的SQL语句 #

SELECT *
FROM time_interval
WHERE start_time < '2024-02-10 17:00:00'
  AND (end_time is null or end_time > '2024-02-10 10:00:00');

逻辑验证与边界分析 #

你的SQL逻辑是完善的，完全覆盖了所有需要考虑的边界情况，具体分析如下：

核心逻辑拆解 #

判断两个区间[A_start, A_end]（目标区间：A_start='2024-02-10 10:00:00'，A_end='2024-02-10 17:00:00'）和[B_start, B_end]（数据表中的区间，B_end可为null代表未结束）是否重叠，等价于排除完全不重叠的两种情况：

• B区间完全在A区间左侧：B_end <= A_start
• B区间完全在A区间右侧：B_start >= A_end

你的SQL通过反向筛选排除了这两种情况：

• start_time < '2024-02-10 17:00:00' 排除了B_start >= A_end的情况
• (end_time is null or end_time > '2024-02-10 10:00:00') 排除了B_end <= A_start的情况（同时处理了B_end为null的pending区间，这类区间不会满足B_end <= A_start，直接保留）

覆盖的边界情况 #

1. pending区间（end_time为null）：只要start_time < A_end就会被选中，符合需求（这类区间持续未结束，必然和目标区间重叠或包含目标区间）
2. 区间刚好在目标区间左边界结束：比如end_time='2024-02-10 10:00:00'，会被end_time > '2024-02-10 10:00:00'排除，这类区间和目标区间无重叠（仅衔接），符合需求
3. 区间刚好在目标区间右边界开始：比如start_time='2024-02-10 17:00:00'，会被start_time < '2024-02-10 17:00:00'排除，这类区间和目标区间无重叠，符合需求
4. 区间完全包含目标区间：比如start_time='2024-02-10 08:00:00'，end_time='2024-02-10 18:00:00'，会被正常选中，符合需求
5. 区间被目标区间完全包含：比如start_time='2024-02-10 12:00:00'，end_time='2024-02-10 14:00:00'，会被正常选中，符合需求
6. 区间左重叠目标区间：比如start_time='2024-02-10 09:00:00'，end_time='2024-02-10 11:00:00'，会被选中，符合需求
7. 区间右重叠目标区间：比如start_time='2024-02-10 16:00:00'，end_time='2024-02-10 18:00:00'，会被选中，符合需求

关于UNION的必要性 #

不需要用UNION区分pending和已结束区间，你的简化版SQL已经通过(end_time is null or end_time > ...)的逻辑完美处理了两种情况，代码更简洁高效。

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