如何修复PostgreSQL牙科预约系统的可用时间槽过滤逻辑？

问题根源 #

你遇到的问题核心是查询逻辑错误地将预约的起始时间点标记为不可用，同时系统可能错误要求结束时间点必须处于可用状态。当已有7:00-7:30和8:00-8:30的预约时，8:00作为后者的起始时间，会被原查询判定为处于8:00-8:30的占用区间内（因为h.hora >= 8:00 AND h.hora < 8:30），导致无法选择8:00作为新预约的结束时间，进而无法创建7:30-8:00的预约。

此外，原查询基于单个时间点的可用性判断，而非预约场景需要的时间段连续性验证，这是设计上的核心偏差。

修复方案 #

方案1：修正预约可用性验证逻辑（优先推荐） #

放弃单个时间点的可用性查询，改为直接验证用户选择的整个时间段是否与已有预约重叠。这是预约系统的标准逻辑：

验证时间段是否可用的SQL

-- $1: 目标日期, $2: 牙医ID, $3: 新预约开始时间(对应Horarios的hora), $4: 新预约结束时间(对应Horarios的hora)
SELECT NOT EXISTS (
    SELECT 1
    FROM Consultas c
    JOIN Horarios h_inic ON c.hora_inic = h_inic.id_horario
    JOIN Horarios h_fin ON c.hora_fin = h_fin.id_horario
    WHERE c.fecha_designada = $1
      AND c.dentista = $2
      AND NOT c.consulta_completa
      -- 核心：判断两个时间段是否重叠
      AND h_inic.hora < $4
      AND h_fin.hora > $3
);

如果返回true，说明该时间段可以预约；返回false则表示与已有预约冲突。

方案2：修复原可用时间点查询（适配现有系统） #

如果必须保留单个时间点的展示逻辑，修改查询以排除已占用的时间点，但允许将已预约的起始时间作为其他预约的结束时间：

修正后的可用时间点查询

SELECT
    h.id_horario,
    h.hora 
FROM 
    Horarios h
WHERE
    NOT EXISTS (
        SELECT 1
        FROM Consultas c
        JOIN Horarios h_inic ON c.hora_inic = h_inic.id_horario
        JOIN Horarios h_fin ON c.hora_fin = h_fin.id_horario
        WHERE
            c.fecha_designada = $1
            AND c.dentista = $2
            AND NOT c.consulta_completa
            -- 仅排除处于预约区间内的时间点（不包含结束时间）
            AND h.hora >= h_inic.hora
            AND h.hora < h_fin.hora
    )
ORDER BY
    h.hora;

此时8:00仍会被标记为不可用（因为它是8:00-8:30预约的起始点，处于该区间内），所以需要在前端逻辑中调整：结束时间无需从可用时间点中选择，只需选择晚于开始时间的合法时间点，最终提交时用方案1的SQL验证整个时间段。

方案3：优化表设计（长期维护推荐） #

原表设计通过外键关联单个时间点表示时间段，增加了查询复杂度。可以简化为直接存储时间值：

修改表结构

-- 移除原有的外键字段
ALTER TABLE Consultas
DROP COLUMN hora_inic,
DROP COLUMN hora_fin;

-- 添加直接存储时间的字段
ALTER TABLE Consultas
ADD COLUMN hora_inic TIME NOT NULL,
ADD COLUMN hora_fin TIME NOT NULL;

如果需要限制只能选择固定时间点（如整点/半点），可以保留Horarios表作为前端可选值的数据源，但无需在Consultas中关联外键，直接存储选中的时间值即可。

简化后的时间段验证SQL

SELECT NOT EXISTS (
    SELECT 1
    FROM Consultas c
    WHERE c.fecha_designada = $1
      AND c.dentista = $2
      AND NOT c.consulta_completa
      AND c.hora_inic < $3
      AND c.hora_fin > $2
);

这个查询性能更高，逻辑也更清晰。

开发指导 #

1. 前端交互：让用户选择开始时间和预约时长（如30分钟、60分钟），自动计算结束时间，避免用户手动选择结束时间，减少逻辑复杂度。
2. 数据一致性：在Consultas表中添加约束，确保hora_fin > hora_inic，避免无效的时间段。
3. 索引优化：为Consultas表的fecha_designada、dentista字段创建联合索引，提升预约验证查询的性能。

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