PostgreSQL跨天timerange创建：结束时间早于开始时间的实现

给定场景：

• 开始时间：'22:00'
• 结束时间：'3:00'（早于开始时间，属于跨午夜的时间范围）

直接计算时间差会得到负数结果：

select time '22:00' - time '3:00'; -- 结果为 interval '-18 hours'

如果要计算实际跨越的小时数，可以用：

SELECT 24 - abs(extract(hour from time '1:00' - time '22:00')); -- 返回3

核心问题 #

当结束时间（上边界）早于开始时间（下边界）时，如何创建合法的ext.timerange？

参考定义 #

已自定义的时间范围类型及辅助函数：

CREATE FUNCTION ext.time_subtype_diff(x time, y time) RETURNS float8 AS 
'SELECT EXTRACT(EPOCH FROM (x - y))' LANGUAGE sql STRICT IMMUTABLE;

CREATE TYPE ext.timerange AS RANGE (
    subtype = time,
    subtype_diff = time_subtype_diff
);

解决方案 #

由于PostgreSQL的Range类型默认要求下边界 ≤ 上边界，直接用ext.timerange('22:00', '3:00')会抛出无效范围的错误。针对跨午夜的场景，推荐以下两种方案：

方案1：使用多范围（timemultirange）合并两段时间 #

将跨午夜的范围拆分为「开始时间到当天结束」和「次日开始到结束时间」两个子范围，合并为一个多范围：

-- 创建跨午夜的时间多范围
SELECT 
  ext.timerange(start_time, '24:00', '[)') || ext.timerange('00:00', end_time, '[)') AS cross_midnight_range
FROM (
  SELECT time '22:00' AS start_time, time '3:00' AS end_time
) t;

该方案能准确表示跨天的时间区间，且支持Range类型的大部分操作（如包含判断、重叠检查等）。

方案2：自定义支持循环的时间范围类型（进阶） #

如果需要用单个Range表示跨午夜区间，可以修改自定义类型的逻辑，让subtype_diff函数支持循环计算（即当x < y时，返回(x + interval '24 hours') - y的epoch）：

-- 重新定义支持循环的时间差函数
CREATE OR REPLACE FUNCTION ext.time_subtype_diff_cyclic(x time, y time) RETURNS float8 AS $$
SELECT 
  EXTRACT(EPOCH FROM (
    CASE WHEN x >= y THEN x - y ELSE (x + interval '24 hours') - y END
  ))
$$ LANGUAGE sql STRICT IMMUTABLE;

-- 创建支持循环的timerange类型
CREATE TYPE ext.timerange_cyclic AS RANGE (
    subtype = time,
    subtype_diff = ext.time_subtype_diff_cyclic
);

此时可以直接创建跨午夜的单个范围：

SELECT ext.timerange_cyclic('22:00', '3:00');

注意：这种自定义循环范围的行为与默认Range不同，使用前需验证业务逻辑是否适配。

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