如何在PostgreSQL中创建可循环的自定义TIME Range类型？

问题核心 #

PostgreSQL的原生范围类型（包括自定义范围类型）基于线性连续空间设计，硬性要求范围下界必须小于等于上界。而time类型属于00:00-23:59的循环时间空间，直接用time作为子类型定义范围时，20:00:00到08:00:00这类跨午夜区间会被判定为下界大于上界，触发range lower bound must be less than or equal to range upper bound错误。自定义subtype_diff函数仅用于优化范围操作的计算逻辑，无法修改这个核心约束。

可行解决方案 #

方案1：使用timestamp范围类型（最简单）

将时间锚定到任意具体日期，把跨午夜范围存成跨天的timestamp区间，查询时忽略日期部分即可。PostgreSQL原生提供tsrange类型，无需自定义：

-- 创建存储营业时间的表
CREATE TABLE business_hours (
    id INT PRIMARY KEY,
    opening_hours tsrange NOT NULL
);

-- 插入跨午夜的营业时间（示例日期可任意选择，查询不依赖具体日期）
INSERT INTO business_hours VALUES (1, '[2024-01-01 20:00:00, 2024-01-02 08:00:00)');

-- 查询凌晨2点是否在营业范围内
SELECT * FROM business_hours
WHERE opening_hours @> '2024-01-02 02:00:00'::timestamp;

方案2：用整数秒数替代time，自定义范围类型

把时间转换为一天内的秒数（0-86399），跨午夜范围可表示为[起始秒数, 起始秒数+结束秒数]（结束秒数小于起始秒数时，加上一天的秒数86400）：

-- 自定义基于整数的范围类型
CREATE TYPE second_range AS RANGE (subtype = integer);

-- 创建表
CREATE TABLE business_hours (
    id INT PRIMARY KEY,
    opening_hours second_range NOT NULL
);

-- 20:00对应72000秒，08:00对应28800秒，跨午夜范围存为[72000, 100800]
INSERT INTO business_hours VALUES (1, '[72000, 100800]'::second_range);

-- 查询当前时间是否在营业范围内
SELECT * FROM business_hours
WHERE (EXTRACT(EPOCH FROM CURRENT_TIME)::integer BETWEEN lower(opening_hours) AND upper(opening_hours))
   OR (EXTRACT(EPOCH FROM CURRENT_TIME)::integer + 86400 BETWEEN lower(opening_hours) AND upper(opening_hours));

方案3：直接存储两个time字段（最直观）

放弃范围类型，直接存储open_time和close_time两个字段，查询时通过逻辑判断处理跨午夜场景：

-- 创建表
CREATE TABLE business_hours (
    id INT PRIMARY KEY,
    open_time TIME NOT NULL,
    close_time TIME NOT NULL
);

-- 插入跨午夜的营业时间
INSERT INTO business_hours VALUES (1, '20:00:00', '08:00:00');

-- 查询凌晨2点是否在营业范围内
SELECT * FROM business_hours
WHERE (open_time < close_time AND '02:00:00' BETWEEN open_time AND close_time)
   OR (open_time > close_time AND ('02:00:00' >= open_time OR '02:00:00' <= close_time));

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