如何从availability与blocked_availability表查询≥X分钟的可用时段

要判断是否存在至少60分钟的可用时段，核心是先拆分出所有未被占用的可用区间，再计算每个区间的时长，最后验证是否有符合要求的区间。以下是通用的SQL解决方案（以MySQL为例，其他数据库可调整时间函数）：

WITH available_windows AS (
    -- 关联可用时段与对应时段内的所有阻塞记录，按时间排序
    SELECT 
        a.day,
        a.start AS avail_start,
        a.end AS avail_end,
        b.start AS block_start,
        b.end AS block_end,
        ROW_NUMBER() OVER (PARTITION BY a.day, a.start, a.end ORDER BY b.start) AS rn
    FROM availability a
    LEFT JOIN blocked_availability b 
        ON a.day = b.day 
        AND b.start >= a.start 
        AND b.end <= a.end
    
    -- 1. 可用时段起始到第一个阻塞时段的区间
    UNION ALL
    SELECT 
        day,
        avail_start,
        block_start,
        NULL,
        NULL,
        0
    FROM (
        SELECT 
            a.day,
            a.start AS avail_start,
            MIN(b.start) AS block_start
        FROM availability a
        LEFT JOIN blocked_availability b 
            ON a.day = b.day 
            AND b.start >= a.start 
            AND b.end <= a.end
        GROUP BY a.day, a.start
        HAVING block_start IS NOT NULL
    ) t
    
    -- 2. 两个阻塞时段之间的间隙区间
    UNION ALL
    SELECT 
        day,
        prev_block_end,
        curr_block_start,
        NULL,
        NULL,
        0
    FROM (
        SELECT 
            a.day,
            b1.end AS prev_block_end,
            b2.start AS curr_block_start
        FROM availability a
        JOIN blocked_availability b1 
            ON a.day = b1.day 
            AND b1.start >= a.start 
            AND b1.end <= a.end
        JOIN blocked_availability b2 
            ON a.day = b2.day 
            AND b2.start >= a.start 
            AND b2.end <= a.end
            AND b2.start > b1.end
        WHERE NOT EXISTS (
            SELECT 1 FROM blocked_availability b3
            WHERE b3.day = a.day
            AND b3.start > b1.end
            AND b3.end < b2.start
        )
    ) t
    
    -- 3. 最后一个阻塞时段到可用时段结束的区间
    UNION ALL
    SELECT 
        day,
        block_end,
        avail_end,
        NULL,
        NULL,
        0
    FROM (
        SELECT 
            a.day,
            a.end AS avail_end,
            MAX(b.end) AS block_end
        FROM availability a
        LEFT JOIN blocked_availability b 
            ON a.day = b.day 
            AND b.start >= a.start 
            AND b.end <= a.end
        GROUP BY a.day, a.end
        HAVING block_end IS NOT NULL
    ) t
    
    -- 4. 无阻塞记录的完整可用时段
    UNION ALL
    SELECT 
        day,
        start,
        end,
        NULL,
        NULL,
        0
    FROM availability a
    WHERE NOT EXISTS (
        SELECT 1 FROM blocked_availability b
        WHERE b.day = a.day
        AND b.start >= a.start
        AND b.end <= a.end
    )
)
-- 检查是否存在时长≥60分钟的可用区间
SELECT EXISTS (
    SELECT 1
    FROM available_windows
    WHERE 
        avail_start IS NOT NULL 
        AND avail_end IS NOT NULL
        AND TIMESTAMPDIFF(MINUTE, STR_TO_DATE(avail_start, '%H:%i'), STR_TO_DATE(avail_end, '%H:%i')) >= 60
) AS has_60min_window;

关键逻辑说明 #

• 通过CTE available_windows 生成所有可能的可用区间，覆盖了无阻塞的完整时段、阻塞前的时段、阻塞间隙、阻塞后的时段四种场景。
• 使用TIMESTAMPDIFF计算区间时长（MySQL语法），若使用PostgreSQL可替换为EXTRACT(EPOCH FROM (avail_end::TIME - avail_start::TIME))/60 >= 60。
• 最终通过EXISTS返回布尔结果（1表示存在符合要求的时段，0表示不存在）。

针对你的示例数据，该查询会返回1，因为14:30-16:00区间时长为90分钟，满足≥60分钟的要求。

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