PostgreSQL跨天时间区间判断BETWEEN语句返回结果异常问题

错误原因 #

你使用的BETWEEN语法默认要求左边界值小于右边界值，而TIME类型是按当日24小时的数值排序的，22:00:00的排序优先级远高于06:00:00，因此不存在任何时间能满足大于等于22点且小于等于6点的条件，自然返回false。

针对22点到次日6点场景的正确写法 #

直接拆分两个判断条件用OR连接即可：

-- 该语句会正确返回true
SELECT '04:00:00'::TIME >= '22:00:00'::TIME OR '04:00:00'::TIME <= '06:00:00'::TIME;

通用兼容写法 #

如果需要同时兼容跨午夜和不跨午夜的两种区间判断，可以封装为通用函数，示例（PostgreSQL语法）：

CREATE OR REPLACE FUNCTION time_between_cross_day(check_t TIME, start_t TIME, end_t TIME)
RETURNS BOOLEAN AS $$
SELECT CASE
    WHEN start_t < end_t THEN check_t BETWEEN start_t AND end_t
    ELSE check_t >= start_t OR check_t <= end_t
END;
$$ LANGUAGE sql IMMUTABLE;

调用示例：

-- 验证4点是否属于22点到6点区间，返回t
SELECT time_between_cross_day('04:00:00', '22:00:00', '06:00:00');
-- 验证13点是否属于10点到18点区间，返回t
SELECT time_between_cross_day('13:00:00', '10:00:00', '18:00:00');

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