日期时间范围（TSRANGE / TSTZRANGE / DATERANGE）

日期时间范围是范围类型中产量最高的一类：预约、排班、酒店房间、会员有效期、促销时段，本质都是"某段时间被占用"。本节讲解 tsrange、tstzrange、daterange 三者的区别、时区陷阱，以及如何用排除约束（Exclusion Constraint）从数据库层面杜绝重叠。

前置知识

阅读本节前，建议先了解：数值范围（INT4RANGE / NUMRANGE）

基础概念

三种日期时间范围

类型	子类型	存储	相当于	是否规范化
`tsrange`	`timestamp without time zone`	变长	不带时区的时间点	否
`tstzrange`	`timestamp with time zone`	变长	带时区的时间点	否
`daterange`	`date`	变长	日历日	是（离散）

选择口诀：跨时区、多地区业务一律用 tstzrange；只关心"哪一天"用 daterange；tsrange 只在明确知道所有时间都是同一时区、且不需要时区换算时使用。

`daterange` 也做规范化

timestamp 是连续类型，不会规范化；但 date 是离散类型（今天 + 1 天就是明天），因此 daterange 会被规范化成 [) 形式。

sql

SELECT '[2026-01-01,2026-01-31]'::daterange AS a,   -- [2026-01-01,2026-02-01)
       '[2026-01-01,2026-01-31)'::daterange AS b;   -- [2026-01-01,2026-01-31)

-- 结果：
--             a              |             b
-- ---------------------------+---------------------------
--  [2026-01-01,2026-02-01)   | [2026-01-01,2026-01-31)

对 daterange 而言，规范化带来的好处同样明显：[2026-01-01,2026-01-31) 与 [2026-01-31,2026-02-15) 相邻但不重叠，正好对应"1 月 1 日到 1 月 30 日"和"1 月 31 日起"，不会出现同一天被算两次的问题。

为什么时间区间推荐 `[)`

写法	含义	相邻区间（如 9:00-10:00 与 10:00-11:00）
`[a,b]`	左闭右闭	10:00 这个瞬间同时属于两段，重叠
`(a,b)`	左开右开	9:00 与 11:00 两端都丢了
`[a,b)`	左闭右开	不重叠、无空隙，完美

因此时间段的黄金标准是 [)。除非业务上明确要求"结束时刻也算在内"（例如按整点计费的计时停车），才使用 []。

无界时间范围

sql

'[,)'::tstzrange       -- 全时段，永不空
'[2026-01-01,)'::tstzrange   -- 2026 年起，长期有效
'(,2026-01-01)'::tstzrange   -- 2026 年前的历史数据

'[,)' 这种"永恒区间"在表示"某条定价规则从今往后一直生效"时非常有用。

术语提示

日期时间范围（Date/Time Range）、时区（Time Zone）、排除约束（Exclusion Constraint）、规范化（Canonicalization）、无界（Unbounded）。本节围绕它们展开。

语法与用法

字面量

text

'[2026-01-01 09:00, 2026-01-01 10:30)'::tsrange
'[2026-01-01 09:00+08, 2026-01-01 10:30+08)'::tstzrange
'[2026-01-01, 2026-02-01)'::daterange
'empty'::tstzrange

tstzrange 的字面量必须带时区偏移（或依赖会话的 TimeZone 设置），否则会出现"同一个字符串按不同会话解释成不同时刻"的隐蔽 bug。

sql

-- 会话时区不同，同一字面量解析出的绝对时刻不同
SET TimeZone = 'Asia/Shanghai';
SELECT '[2026-01-01 09:00, 2026-01-01 10:00)'::tstzrange;   -- ["2026-01-01 09:00:00+08","2026-01-01 10:00:00+08")

SET TimeZone = 'UTC';
SELECT '[2026-01-01 09:00, 2026-01-01 10:00)'::tstzrange;   -- ["2026-01-01 09:00:00+00","2026-01-01 10:00:00+00")

上面两条语句的文本看起来一样，但内部存储的绝对时间是不同的。这就是 tstzrange 字面量必须显式带偏移量的原因。

构造函数

text

tsrange(lower, upper)
tsrange(lower, upper, bounds)          -- bounds ∈ '[)' '(]' '[]' '()'
tsrange(lower, upper, bounds, 'empty') -- 构造空范围
tstzrange(lower, upper)
daterange(lower, upper)
daterange(lower, upper, bounds)

sql

SELECT tsrange('2026-01-01 09:00', '2026-01-01 10:30')                       AS a,  -- ["2026-01-01 09:00:00","2026-01-01 10:30:00")
       tsrange('2026-01-01 09:00', '2026-01-01 10:30', '[]')                 AS b,  -- ["2026-01-01 09:00:00","2026-01-01 10:30:00"]
       tsrange('2026-01-01 09:00', '2026-01-01 10:30', '()')                 AS c,  -- ("2026-01-01 09:00:00","2026-01-01 10:30:00")
       tstzrange('2026-01-01 09:00+08', '2026-01-01 10:30+08')               AS d,
       daterange('2026-01-01', '2026-02-01')                                 AS e;  -- [2026-01-01,2026-02-01)

示例表

sql

CREATE TABLE bookings (
  id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  room_id    TEXT      NOT NULL,               -- 房间号
  booker     TEXT      NOT NULL,               -- 预订人
  period     TSTZRANGE NOT NULL,               -- 占用时段
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

INSERT INTO bookings (room_id, booker, period) VALUES
  ('A101', '张三', '[2026-03-01 09:00+08, 2026-03-01 11:00+08)'),
  ('A101', '李四', '[2026-03-01 11:00+08, 2026-03-01 12:00+08)'),  -- 与上一条相邻，允许
  ('A102', '王五', '[2026-03-01 09:00+08, 2026-03-01 18:00+08)');

详细说明

`tsrange` 与 `tstzrange` 的本质差异

维度	`tsrange`	`tstzrange`
子类型	`timestamp`（无时区）	`timestamptz`（内部存 UTC 绝对时间）
存储内容	字面量原样（"墙上时钟"读数）	绝对时刻（UTC 微秒数）
会话时区影响	不影响存取	影响显示与字面量解析
跨时区比较	危险，语义模糊	安全
夏令时（DST）	夏令时切换日可能出现不存在的时刻	由 UTC 偏移自动处理
推荐场景	单一固定时区的业务（少）	绝大多数业务

sql

-- tsrange 不关心时区：字面量怎么写就怎么存
CREATE TEMP TABLE demo_ts (p tsrange);
SET TimeZone = 'Asia/Shanghai';
INSERT INTO demo_ts (p) VALUES ('[2026-06-01 09:00, 2026-06-01 10:00)');
SET TimeZone = 'America/New_York';
SELECT p FROM demo_ts;   -- 仍然是 ["2026-06-01 09:00:00","2026-06-01 10:00:00")，读数没变

sql

-- tstzrange 会带时区显示：同一条记录在不同时区下显示不同的本地时间
CREATE TEMP TABLE demo_tstz (p tstzrange);
SET TimeZone = 'Asia/Shanghai';
INSERT INTO demo_tstz (p) VALUES ('[2026-06-01 09:00+08, 2026-06-01 10:00+08)');
SELECT p FROM demo_tstz;   -- ["2026-06-01 09:00:00+08","2026-06-01 10:00:00+08")

SET TimeZone = 'America/New_York';
SELECT p FROM demo_tstz;   -- ["2026-05-31 21:00:00-04","2026-05-31 22:00:00-04")

注意第三条语句插入时只写了 09:00（未带偏移），它按当时的会话时区 Asia/Shanghai 解析；一旦切换时区再查询，显示的就是换算后的本地时间。

同一个记录，纽约同事看到的是 5 月 31 日 21 点。这不是 bug——两家办公室对"同一时刻"的本地表述本来就不同。

版本提示

tsrange / tstzrange / daterange 自 PostgreSQL 9.2 起提供，行为在后续版本中保持稳定。PostgreSQL 14 起新增了对应的多范围类型（tsmultirange / tstzmultirange / datemultirange），用于表示"不连续的多个时段"，详见范围类型概述。

`daterange` 的离散边界

daterange 的规范化让"待几天"的语义变得清晰：

sql

SELECT '[2026-01-01,2026-01-03]'::daterange AS a,   -- [2026-01-01,2026-01-04)  含 1、2、3 号共 3 天
       '[2026-01-01,2026-01-03)'::daterange AS b,   -- [2026-01-01,2026-01-03)  含 1、2 号共 2 天
       upper('[2026-01-01,2026-01-03)')::date AS b_upper;  -- 2026-01-03

-- 计算天数（上界 - 下界）
SELECT upper('[2026-01-01,2026-01-04)') - lower('[2026-01-01,2026-01-04)') AS nights;  -- 3

酒店场景的天数用 daterange 表达最自然：[入住日, 退房日) 的差就是住几晚。

查看与拆解范围

sql

SELECT
  lower(period)  AS start_at,     -- 下界
  upper(period)  AS end_at,       -- 上界
  lower_inc(period) AS low_closed,    -- 下界是否包含
  upper_inc(period) AS up_closed,     -- 上界是否包含
  upper(period) - lower(period) AS duration
FROM bookings
WHERE room_id = 'A101';

text

    start_at    |     end_at      | low_closed | up_closed | duration
----------------+-----------------+------------+-----------+----------
 2026-03-01 09:00:00+08 | 2026-03-01 11:00:00+08 | t | f | 02:00:00
 2026-03-01 11:00:00+08 | 2026-03-01 12:00:00+08 | t | f | 01:00:00

duration 列能直接相减，是因为 tstzrange 的两个边界都是 timestamptz，减法返回 interval。这比"两个独立字段 + 应用层算差值"省事得多。

用排除约束杜绝重叠

这是日期时间范围真正的杀手锏：数据库层面保证同房间同时段只有一条预约。

sql

CREATE TABLE room_bookings (
  id       BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  room_id  TEXT      NOT NULL,
  booker   TEXT      NOT NULL,
  period   TSTZRANGE NOT NULL,
  CONSTRAINT chk_not_empty CHECK (NOT isempty(period)),
  CONSTRAINT no_overlap_room
    EXCLUDE USING GIST (room_id WITH =, period WITH &&)   -- 同房间 && 重叠 → 拒绝
);

-- 需要 btree_gist 扩展才能把 TEXT 的 = 用在 GiST 排除约束里
CREATE EXTENSION IF NOT EXISTS btree_gist;

sql

INSERT INTO room_bookings (room_id, booker, period) VALUES
  ('A101', '张三', '[2026-03-01 09:00+08, 2026-03-01 11:00+08)');
-- INSERT 0 1

-- 相邻（11:00 开始）不重叠，允许
INSERT INTO room_bookings (room_id, booker, period) VALUES
  ('A101', '李四', '[2026-03-01 11:00+08, 2026-03-01 12:00+08)');
-- INSERT 0 1

-- 与第一条重叠（10:00 开始），被拒绝
INSERT INTO room_bookings (room_id, booker, period) VALUES
  ('A101', '赵六', '[2026-03-01 10:00+08, 2026-03-01 11:30+08)');

text

ERROR:  conflicting key value violates exclusion constraint "no_overlap_room"
DETAIL:  Key (room_id, period)=(A101, ["2026-03-01 10:00:00+08","2026-03-01 11:30:00+08")) conflicts with existing key (room_id, period)=(A101, ["2026-03-01 09:00:00+08","2026-03-01 11:00:00+08")).

不同房间同时段则互不冲突：

sql

INSERT INTO room_bookings (room_id, booker, period) VALUES
  ('A102', '王五', '[2026-03-01 10:00+08, 2026-03-01 11:30+08)');
-- INSERT 0 1   room_id 不同，不冲突

时区与夏令时陷阱

sql

-- 美国夏令时切换日：2026-03-08 凌晨 2:00 → 3:00，本地时间 2:30 不存在
SET TimeZone = 'America/New_York';
SELECT '[2026-03-08 02:30-05, 2026-03-08 04:00-04)'::tstzrange;
-- ["2026-03-08 02:30:00-05","2026-03-08 04:00:00-04")  偏移量随 DST 变化

-- tsrange 完全不知道自己跨越了 DST，两个"墙上时钟"之差按普通时间计算
SELECT upper(tsrange('2026-03-08 01:30', '2026-03-08 04:00'))
     - lower(tsrange('2026-03-08 01:30', '2026-03-08 04:00')) AS wall_clock_diff;
-- 02:30:00   实际经历的是 1 小时 30 分（2:00 → 3:00 这一小时被跳过）

text

 wall_clock_diff
-----------------
 02:30:00

结论：只要业务涉及夏令时地区，就用 tstzrange 而非 tsrange。

索引支持

索引	加速的操作	备注
GiST	`&&` `@>` `<@` `=` `-	-`
SP-GiST	同上，部分场景更快	不支持 `=` 的排除约束
B-tree	无（范围无法整体排序）	不能直接对范围列建 B-tree 做范围查询

sql

CREATE INDEX idx_bookings_period ON bookings USING GIST (period);
CREATE INDEX idx_bookings_room_period ON bookings USING GIST (room_id, period);  -- 多列需 btree_gist

实战示例

下面用 room_bookings 表演示一套完整的排期流程。

第 1 步：准备表与扩展

sql

CREATE EXTENSION IF NOT EXISTS btree_gist;   -- 让 TEXT 的 = 可用于 GiST 排除约束

DROP TABLE IF EXISTS room_bookings;
CREATE TABLE room_bookings (
  id       BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  room_id  TEXT      NOT NULL,
  booker   TEXT      NOT NULL,
  period   TSTZRANGE NOT NULL,
  CONSTRAINT chk_not_empty CHECK (NOT isempty(period)),
  CONSTRAINT no_overlap_room
    EXCLUDE USING GIST (room_id WITH =, period WITH &&)
);
CREATE INDEX idx_room_bookings_period ON room_bookings USING GIST (period);

第 2 步：写入正常预约

sql

INSERT INTO room_bookings (room_id, booker, period) VALUES
  ('A101', '张三', '[2026-03-01 09:00+08, 2026-03-01 11:00+08)'),
  ('A101', '李四', '[2026-03-01 11:00+08, 2026-03-01 12:00+08)'),
  ('A101', '王五', '[2026-03-01 14:00+08, 2026-03-01 16:00+08)'),
  ('A102', '赵六', '[2026-03-01 09:00+08, 2026-03-01 18:00+08)');
-- INSERT 0 4

第 3 步：验证防重叠约束

sql

INSERT INTO room_bookings (room_id, booker, period) VALUES
  ('A101', '冲突者', '[2026-03-01 10:00+08, 2026-03-01 11:30+08)');

text

ERROR:  conflicting key value violates exclusion constraint "no_overlap_room"
DETAIL:  Key (room_id, period)=(A101, ["2026-03-01 10:00:00+08","2026-03-01 11:30:00+08")) conflicts with existing key (room_id, period)=(A101, ["2026-03-01 09:00:00+08","2026-03-01 11:00:00+08")).

第 4 步：查询某时刻哪些房间空闲

sql

-- 2026-03-01 10:00 时刻，A101 被占用、A102 也被占用
SELECT room_id, booker
FROM room_bookings
WHERE period @> '2026-03-01 10:00+08'::timestamptz;

text

 room_id | booker
---------+--------
 A101    | 张三
 A102    | 赵六

找出该时刻空闲的房间，需要一张房间台账表做反连接：

sql

SELECT r.room_id
FROM (VALUES ('A101'), ('A102'), ('A103')) AS r(room_id)
WHERE NOT EXISTS (
  SELECT 1 FROM room_bookings b
  WHERE b.room_id = r.room_id
    AND b.period @> '2026-03-01 10:00+08'::timestamptz
);

text

 room_id
---------
 A103

第 5 步：查询与某时段重叠的预约

sql

SELECT room_id, booker, period
FROM room_bookings
WHERE period && '[2026-03-01 10:00+08, 2026-03-01 15:00+08)'::tstzrange
ORDER BY room_id, lower(period);

text

 room_id | booker |                    period
---------+--------+-----------------------------------------------
 A101    | 张三   | ["2026-03-01 09:00:00+08","2026-03-01 11:00:00+08")
 A101    | 王五   | ["2026-03-01 14:00:00+08","2026-03-01 16:00:00+08")
 A102    | 赵六   | ["2026-03-01 09:00:00+08","2026-03-01 18:00:00+08")

相邻的 李四（11:00-12:00）被正确排除——它只与查询区间的边界点相接，不算重叠。

第 6 步：拆解为可读的起止时间

sql

SELECT room_id,
       booker,
       to_char(lower(period) AT TIME ZONE 'Asia/Shanghai', 'YYYY-MM-DD HH24:MI') AS start_local,
       to_char(upper(period) AT TIME ZONE 'Asia/Shanghai', 'YYYY-MM-DD HH24:MI') AS end_local,
       upper(period) - lower(period) AS duration
FROM room_bookings
WHERE room_id = 'A101'
ORDER BY lower(period);

text

 room_id | booker |   start_local   |    end_local    | duration
---------+--------+-----------------+-----------------+----------
 A101    | 张三   | 2026-03-01 09:00 | 2026-03-01 11:00 | 02:00:00
 A101    | 李四   | 2026-03-01 11:00 | 2026-03-01 12:00 | 01:00:00
 A101    | 王五   | 2026-03-01 14:00 | 2026-03-01 16:00 | 02:00:00

第 7 步：合并某房间的全部占用时段

sql

-- range_agg 把多行范围聚合成多范围（PostgreSQL 14+）
SELECT room_id, range_agg(period) AS all_periods
FROM room_bookings
GROUP BY room_id
ORDER BY room_id;

text

 room_id |                     all_periods
---------+------------------------------------------------------
 A101    | {["2026-03-01 09:00:00+08","2026-03-01 12:00:00+08"),["2026-03-01 14:00:00+08","2026-03-01 16:00:00+08")}
 A102    | {["2026-03-01 09:00:00+08","2026-03-01 18:00:00+08")}

A101 的前两段（09-11、11-12）因为相邻自动被合并成 09-12。

版本提示

range_agg 与 range_intersect_agg 是 PostgreSQL 14 新增的聚合函数，PostgreSQL 13 及更早版本不可用。低版本可用 array_agg(period) 暂存后再在应用层合并。

第 8 步：清理

sql

DROP TABLE IF EXISTS room_bookings;

注意事项

tstzrange 字面量必须带时区偏移。 依赖会话 TimeZone 会让同一条 SQL 在不同环境下写入不同时刻。
时段一律用 [)。 闭区间会造成边界瞬间重叠，开区间会丢端点。
排除约束需要 GiST，且 = 需要 btree_gist 扩展。 忘记装扩展会直接报"data type text has no default operator class for access method gist"。
EXCLUDE 约束不能使用非 IMMUTABLE 函数。 room_id WITH = 是纯比较，安全；若想用 lower(period) 当键则可能不满足条件。
daterange 会自动规范化。 '[2026-01-01,2026-01-31]' 实际是 [2026-01-01,2026-02-01)，展示时不要直接拼字符串给用户看。
tsrange 不知道夏令时。 跨 DST 的时段时长会算错，涉及 DST 地区请用 tstzrange。
upper(period) - lower(period) 对无界范围返回 NULL。 处理长期有效规则时要用 COALESCE。
空范围 'empty' 与 NULL 不同。 'empty'::tstzrange 是一个合法的"没有任何时刻"的范围，@> 任何时间都返回 false。
&& 会用到 GiST 索引，@> 也会，但 upper(period) > x 这类表达式不会——表达式索引或改写成范围比较才能走索引。
相邻判定用 -|-，不要用 NOT (a && b)。 后者把所有不相交（含相隔很远）都算成真，语义完全不同。
跨天预约若用 daterange 表示，注意"退房日"是上界不含。 [入住, 退房) 是行业惯例，别写成 []。
多范围（Multirange）在 PostgreSQL 14+ 才可用。 旧版本合并时段的替代方案是 unnest + 应用层归并。

最佳实践

默认选 tstzrange。 除非能明确证明全库只有一个固定时区，否则不要用 tsrange。
预约类表一律加 EXCLUDE USING GIST 排除约束。 应用层的"先查再插"在并发下必然漏判，数据库约束才是唯一可靠防线。
同时建 CHECK (NOT isempty(period))。 空范围能通过绝大多数范围操作，却没有任何业务含义，属于脏数据。
为范围列建 GiST 索引。 排除约束会自动创建，但普通查询（&& / @>）需要独立的索引才有好性能。
多列排除约束配合 btree_gist。 (room_id WITH =, period WITH &&) 是"同资源不重叠"的标准写法。
用 lower() / upper() 生成展示给用户的起止时间。 不要让前端自己去解析 ["...","...") 这种文本形式。
AT TIME ZONE 只用于展示。 存储层永远存 tstzrange（内部 UTC），换算成本地时间是查询时的最后一步。
过期数据按范围列分区或归档。 WHERE period && '[now, ...)' 这类查询只关心未来数据，历史数据堆积会拖慢索引。
超过 24 小时的长时段考虑用 daterange + 时刻字段组合。 若业务只按天计费，用 daterange 比 tstzrange 更省空间、更好懂。

继续学习：范围操作符

参考链接

PostgreSQL 官方文档：Range Types
PostgreSQL 官方文档：Range Functions and Operators
PostgreSQL 官方文档：Multirange Types
PostgreSQL 官方文档：Constraints
PostgreSQL 官方文档：Date/Time Types

日期时间范围（TSRANGE / TSTZRANGE / DATERANGE） ​

基础概念 ​

三种日期时间范围 ​

daterange 也做规范化 ​

为什么时间区间推荐 [) ​

无界时间范围 ​

语法与用法 ​

字面量 ​

构造函数 ​

示例表 ​

详细说明 ​

tsrange 与 tstzrange 的本质差异 ​

daterange 的离散边界 ​

查看与拆解范围 ​

用排除约束杜绝重叠 ​

时区与夏令时陷阱 ​

索引支持 ​

实战示例 ​

第 1 步：准备表与扩展 ​

第 2 步：写入正常预约 ​

第 3 步：验证防重叠约束 ​

第 4 步：查询某时刻哪些房间空闲 ​

第 5 步：查询与某时段重叠的预约 ​

日期时间范围（TSRANGE / TSTZRANGE / DATERANGE）

基础概念

三种日期时间范围

`daterange` 也做规范化

为什么时间区间推荐 `[)`

无界时间范围

语法与用法

字面量

构造函数

示例表

详细说明

`tsrange` 与 `tstzrange` 的本质差异

`daterange` 的离散边界

查看与拆解范围

用排除约束杜绝重叠

时区与夏令时陷阱

索引支持

实战示例

第 1 步：准备表与扩展

第 2 步：写入正常预约

第 3 步：验证防重叠约束

第 4 步：查询某时刻哪些房间空闲

第 5 步：查询与某时段重叠的预约