PHP小码农

Appearance

OVERLAPS 时间重叠 ​

判断「两段时间是否重叠」看起来只是两个不等式，但真写起来处处是坑：边界取开还是取闭、端点相接算不算重叠、NULL 出现在哪一侧、以及最要命的——这种写法用不上索引。PostgreSQL 提供了两种表达手段：SQL 标准里的 OVERLAPS 行构造器语法，以及范围类型上的 && 运算符。前者是标准语法但语义固定（闭区间）、无法索引；后者灵活、可走 GiST 索引，还能配合 EXCLUDE 约束从数据库层面杜绝重叠。本节把两者的差异、常见误用与索引方案讲透。

前置知识

阅读本节前，建议先了解：时区转换

基础概念 ​

概念术语说明
时间重叠Overlap两段时间区间存在公共部分
闭区间Closed Interval两端点都包含，记作 [a, b]
半开区间Half-Open Interval含左不含右，记作 [a, b)
行构造器Row Constructor(a, b) 形式的复合值，OVERLAPS 的语法要求
范围重叠运算符&&范围类型上的「重叠」运算符，可走 GiST 索引
范围类型Range Typetsrange / tstzrange / daterange，见日期时间范围
排除约束Exclusion ConstraintEXCLUDE USING GIST，用 && 保证同资源不重叠
相邻Adjacent两段首尾相接但无公共部分，`-

核心差异：OVERLAPS 用闭区间，范围类型用半开区间

text
OVERLAPS（闭区间语义）：

   09:00                11:00   11:00                12:00
     │────────────────────│═══════│────────────────────│
                        ▲
                   端点相同 → 判定为「重叠」！

范围类型 &&（推荐用 [) 半开区间）：

   09:00                11:00   11:00                12:00
     │────────────────────┤       ├────────────────────│
                        ▲
                   端点相同 → 不重叠（无公共部分）

一句话记忆：OVERLAPS 里 [09:00, 11:00] 与 [11:00, 12:00] 算重叠；换成 [) 范围则不算。 预约、排班这类业务里，端点相接必须算「不冲突」，否则 9:00–11:00 之后 11:00–12:00 会被错误拒绝。

语法与用法 ​

sql
-- 1. OVERLAPS 行构造器语法（SQL 标准，PostgreSQL 全版本支持）
(start1, end1) OVERLAPS (start2, end2)              → boolean
-- 两侧必须带括号，且每侧恰好两个时间类型的值

-- 2. 范围类型的 && 运算符（推荐）
range1 && range2                                    → boolean
tsrange(start, stop [, bounds]) && tsrange(...)
tstzrange(start, stop [, bounds]) && tstzrange(...)
daterange(start, stop [, bounds]) && daterange(...)

-- 3. 相关的范围判定
range1 @> value         -- 范围包含某时刻
range1 <@ range2        -- 范围被包含
range1 -|- range2       -- 相邻（首尾相接）

最小可运行示例：

sql
-- 端点相接：OVERLAPS 判定为真
SELECT
  (TIMESTAMP '2026-03-01 09:00', TIMESTAMP '2026-03-01 11:00')
    OVERLAPS
  (TIMESTAMP '2026-03-01 11:00', TIMESTAMP '2026-03-01 12:00') AS o_overlaps,
  tsrange('2026-03-01 09:00', '2026-03-01 11:00', '[)')
    &&
  tsrange('2026-03-01 11:00', '2026-03-01 12:00', '[)')        AS r_overlap;
text
 o_overlaps | r_overlap
------------+-----------
 t          | f

同一个问题，两种写法给出相反的答案。这不是 bug，而是 OVERLAPS 的闭区间语义与范围的半开区间语义在端点上的必然分歧。

准备本节使用的示例表：

sql
-- 会议室预订表：用两个独立的时间戳列存时段
DROP TABLE IF EXISTS meeting_rooms;
CREATE TABLE meeting_rooms (
  id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  room_id    TEXT        NOT NULL,
  booker     TEXT        NOT NULL,
  start_at   TIMESTAMP   NOT NULL,     -- 开始时刻
  end_at     TIMESTAMP   NOT NULL,     -- 结束时刻
  CONSTRAINT chk_time_order CHECK (start_at <= end_at)
);

INSERT INTO meeting_rooms (room_id, booker, start_at, end_at) VALUES
  ('A101', '张三', '2026-03-01 09:00', '2026-03-01 11:00'),
  ('A101', '李四', '2026-03-01 13:00', '2026-03-01 15:00'),
  ('A102', '王五', '2026-03-01 10:00', '2026-03-01 12:00'),
  ('A102', '赵六', '2026-03-01 14:00', '2026-03-01 16:00');

详细说明 ​

必须使用括号包裹的行构造器 ​

OVERLAPS 的两个操作数不是普通的标量，而是行构造器（Row Constructor），语法上必须写成带括号的两个值：

sql
-- 正确：两侧都是 (start, end) 行构造器
SELECT (TIMESTAMP '2026-03-01 09:00', TIMESTAMP '2026-03-01 11:00')
         OVERLAPS
       (TIMESTAMP '2026-03-01 10:00', TIMESTAMP '2026-03-01 12:00') AS ok;
text
 ok
----
 t
sql
-- 错误！缺少括号，解析器把逗号当成了参数分隔
SELECT TIMESTAMP '2026-03-01 09:00', TIMESTAMP '2026-03-01 11:00'
       OVERLAPS
       TIMESTAMP '2026-03-01 10:00', TIMESTAMP '2026-03-01 12:00';
-- ERROR:  syntax error at or near "OVERLAPS"

行构造器的括号不是可选的。这是 OVERLAPS 与普通二元运算符（如 &&）在使用感受上最大的差别。

两侧的类型可以是 timestamp、timestamptz、date，甚至 time。混用不同类型时，PostgreSQL 会先做类型提升：

sql
-- date 与 timestamp 混用：date 被提升为 timestamp（当天 00:00:00）
SELECT (DATE '2026-03-01', DATE '2026-03-02')
         OVERLAPS
       (TIMESTAMP '2026-03-01 23:00', TIMESTAMP '2026-03-02 01:00') AS mixed;
text
 mixed
-------
 t

类型提升规则：date → timestamp → timestamptz，向"更精确、更带时区"的方向统一。但提升的结果依赖会话时区（date 变 timestamptz 时），跨时区业务应显式转换，不要依赖隐式提升。

闭区间语义与端点相接 ​

这是 OVERLAPS 最容易踩的坑，值得单独用实测钉死：

sql
-- 情形 1：完全分离 → false
SELECT (TIMESTAMP '2026-03-01 09:00', TIMESTAMP '2026-03-01 10:00')
         OVERLAPS
       (TIMESTAMP '2026-03-01 11:00', TIMESTAMP '2026-03-01 12:00') AS apart;

-- 情形 2：部分相交 → true
SELECT (TIMESTAMP '2026-03-01 09:00', TIMESTAMP '2026-03-01 11:00')
         OVERLAPS
       (TIMESTAMP '2026-03-01 10:00', TIMESTAMP '2026-03-01 12:00') AS partial;

-- 情形 3：端点相接（11:00 == 11:00）→ true（闭区间！）
SELECT (TIMESTAMP '2026-03-01 09:00', TIMESTAMP '2026-03-01 11:00')
         OVERLAPS
       (TIMESTAMP '2026-03-01 11:00', TIMESTAMP '2026-03-01 12:00') AS touching;

-- 情形 4：完全包含 → true
SELECT (TIMESTAMP '2026-03-01 09:00', TIMESTAMP '2026-03-01 18:00')
         OVERLAPS
       (TIMESTAMP '2026-03-01 10:00', TIMESTAMP '2026-03-01 12:00') AS contained;
text
 apart | partial | touching | contained
-------+---------+----------+-----------
 f     | t       | t        | t

端点相接算重叠，会误伤「排班接续」场景

OVERLAPS 把两个区间都当作闭区间 [start, end]，因此「上一班到 11:00 结束、下一班 11:00 开始」会被判定为重叠。排班、会议室、车辆调度这类「结束时刻即释放资源」的业务，必须改用半开区间写法：

sql
-- 推荐：半开区间 [start, end)
tsrange(start_at, end_at, '[)') && tsrange(?, ?, '[)')

start > end 会被自动交换 ​

OVERLAPS 对参数的顺序是宽容的——两个值谁大谁小都行，它会自动取小者为起点：

sql
-- 刻意写反顺序，结果与写正时完全一致
SELECT (TIMESTAMP '2026-03-01 11:00', TIMESTAMP '2026-03-01 09:00')
         OVERLAPS
       (TIMESTAMP '2026-03-01 10:00', TIMESTAMP '2026-03-01 12:00') AS reversed;

-- 两侧都写反
SELECT (TIMESTAMP '2026-03-01 11:00', TIMESTAMP '2026-03-01 09:00')
         OVERLAPS
       (TIMESTAMP '2026-03-01 12:00', TIMESTAMP '2026-03-01 10:00') AS both_reversed;
text
 reversed | both_reversed
----------+---------------
 t        | t

这与范围类型完全不同：tsrange('2026-03-01 11:00', '2026-03-01 09:00') 会直接报错 range lower bound must be less than or equal to range upper bound。OVERLAPS 宽容、范围类型严格，这也是范围类型更不容易出静默错误的原因之一。

NULL 参与运算：整条表达式返回 NULL ​

只要四个时间值中有任意一个是 NULL，OVERLAPS 就返回 NULL（而不是 false）：

sql
SELECT
  (TIMESTAMP '2026-03-01 09:00', NULL)
    OVERLAPS
  (TIMESTAMP '2026-03-01 10:00', TIMESTAMP '2026-03-01 12:00') AS null_start,
  (NULL, NULL)
    OVERLAPS
  (TIMESTAMP '2026-03-01 10:00', TIMESTAMP '2026-03-01 12:00') AS both_null;
text
 null_start | both_null
------------+-----------
            |

后果是：WHERE NOT (a OVERLAPS b) 不会返回 NULL 行——NOT NULL 仍是 NULL，而 WHERE 只接受 true。要包含 NULL 行必须显式 IS NOT TRUE：

sql
WHERE (a_overlaps_b) IS NOT TRUE

用 && 与范围类型改写 ​

把两个时间戳列包成范围，就得到一个可索引、语义明确的重叠判定：

sql
-- 闭区间等价写法：显式指定 '[]'
SELECT tsrange(start_at, end_at, '[]') AS period FROM meeting_rooms LIMIT 1;

-- 半开区间（推荐）：'[)' 是 tsrange 的默认边界
SELECT tsrange(start_at, end_at) AS period FROM meeting_rooms LIMIT 1;
text
            period
-------------------------------
 ["2026-03-01 09:00:00","2026-03-01 11:00:00")

OVERLAPS 与 && 的语义对照：

目标语义OVERLAPS 写法范围 && 写法
闭区间 [a,b]原生支持（就是它）tsrange(a, b, '[]') && tsrange(c, d, '[]')
半开区间 [a,b)需改写为 a < d AND c < btsrange(a, b) && tsrange(c, d)（默认）
端点相接是否算重叠算[) 下不算
能否自动交换起止可以不可以，写反即报错
能否走索引不能（见下节）GiST 可走
能否用于 EXCLUDE 约束不能能
NULL 处理返回 NULL返回 NULL

半开区间的等价表达，即经典的 a < d AND c < b：

sql
-- 两个区间 [a,b) 与 [c,d) 重叠 ⇔ a < d AND c < b
SELECT count(*) AS overlapping
FROM meeting_rooms
WHERE start_at < TIMESTAMP '2026-03-01 11:00'
  AND TIMESTAMP '2026-03-01 09:00' < end_at;
text
 overlapping
-------------
           2

这条改写非常值得记住：它不依赖任何范围类型，却给出了正确的半开区间语义，而且两个条件都能走 B-tree 索引。

用 EXCLUDE 约束在数据库层杜绝重叠 ​

应用层的「先查后插」在并发下必然漏判——两个请求可能同时查到「无冲突」再各自插入。可靠的防线是排除约束：

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

CREATE TABLE room_reservations (
  id        BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  room_id   TEXT      NOT NULL,
  booker    TEXT      NOT NULL,
  start_at  TIMESTAMP NOT NULL,
  end_at    TIMESTAMP NOT NULL,
  period    TSTZRANGE NOT NULL
              GENERATED ALWAYS AS (tstzrange(start_at, end_at, '[)')) STORED,
  CONSTRAINT chk_period_order CHECK (start_at < end_at),
  CONSTRAINT no_room_overlap
    EXCLUDE USING GIST (room_id WITH =, period WITH &&)
);
sql
INSERT INTO room_reservations (room_id, booker, start_at, end_at)
VALUES ('A101', '张三', '2026-03-01 09:00+08', '2026-03-01 11:00+08');
-- INSERT 0 1

-- 端点相接：半开区间下不冲突，允许
INSERT INTO room_reservations (room_id, booker, start_at, end_at)
VALUES ('A101', '李四', '2026-03-01 11:00+08', '2026-03-01 12:00+08');
-- INSERT 0 1

-- 真正重叠：被拒绝
INSERT INTO room_reservations (room_id, booker, start_at, end_at)
VALUES ('A101', '赵六', '2026-03-01 10:00+08', '2026-03-01 11:30+08');
text
ERROR:  conflicting key value violates exclusion constraint "no_room_overlap"
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")).

生成列与 EXCLUDE 的关系

上面的 period 用 GENERATED ALWAYS AS ... STORED 从两个时间戳列派生，好处是不必让应用层重复维护范围列，也不会出现「时间戳改了、范围没改」的不一致。生成列要求表达式 IMMUTABLE，而 tstzrange(start_at, end_at, '[)') 恰好是 IMMUTABLE，因此可以合法用于生成列与索引。详见日期时间范围（TSRANGE / TSTZRANGE / DATERANGE）。

索引与执行计划 ​

OVERLAPS 是普通函数调用，无法直接使用任何索引；改写为 a < d AND c < b 或使用范围列 && 才能走索引：

sql
-- 方案 A：OVERLAPS（无法索引）
EXPLAIN (ANALYZE, COSTS OFF)
SELECT count(*) FROM meeting_rooms
WHERE (start_at, end_at)
        OVERLAPS
      (TIMESTAMP '2026-03-01 10:00', TIMESTAMP '2026-03-01 12:00');
text
                         QUERY PLAN
-------------------------------------------------------------
 Aggregate (actual time=0.312..0.313 rows=1 loops=1)
   ->  Seq Scan on meeting_rooms (actual time=0.011..0.024 rows=2 loops=1)
         Filter: ((start_at, end_at) OVERLAPS (...))
         Rows Removed by Filter: 2
 Planning Time: 0.115 ms
 Execution Time: 0.352 ms
sql
-- 方案 B：半开区间不等式（可用 B-tree 索引）
CREATE INDEX idx_rooms_start ON meeting_rooms (start_at);

EXPLAIN (ANALYZE, COSTS OFF)
SELECT count(*) FROM meeting_rooms
WHERE start_at < TIMESTAMP '2026-03-01 12:00'
  AND end_at   > TIMESTAMP '2026-03-01 10:00';
text
                                 QUERY PLAN
----------------------------------------------------------------------------
 Aggregate (actual time=0.031..0.032 rows=1 loops=1)
   ->  Bitmap Heap Scan on meeting_rooms (actual time=0.014..0.019 rows=2 loops=1)
         Recheck Cond: (start_at < '2026-03-01 12:00:00'::timestamp without time zone)
         Filter: (end_at > '2026-03-01 10:00:00'::timestamp without time zone)
         Heap Blocks: exact=1
         ->  Bitmap Index Scan on idx_rooms_start (actual time=0.006..0.006 rows=2 loops=1)
               Index Cond: (start_at < '2026-03-01 12:00:00'::timestamp without time zone)
 Planning Time: 0.098 ms
 Execution Time: 0.061 ms
sql
-- 方案 C：范围列 &&（可用 GiST 索引）
ALTER TABLE room_reservations ADD CONSTRAINT dummy;  -- 省略，见上节建表

CREATE INDEX idx_reservations_period ON room_reservations USING GIST (period);

EXPLAIN (ANALYZE, COSTS OFF)
SELECT count(*) FROM room_reservations
WHERE period && tstzrange('2026-03-01 10:00+08', '2026-03-01 12:00+08', '[)');
text
                                 QUERY PLAN
----------------------------------------------------------------------------
 Aggregate (actual time=0.028..0.029 rows=1 loops=1)
   ->  Bitmap Heap Scan on room_reservations (actual time=0.011..0.015 rows=2 loops=1)
         Recheck Cond: (period && '["2026-03-01 10:00:00+08","2026-03-01 12:00:00+08")'::tstzrange)
         Heap Blocks: exact=1
         ->  Bitmap Index Scan on idx_reservations_period (actual time=0.005..0.005 rows=2 loops=1)
               Index Cond: (period && '["2026-03-01 10:00:00+08","2026-03-01 12:00:00+08")'::tstzrange)
 Planning Time: 0.104 ms
 Execution Time: 0.058 ms

数据量只有 4 行时，Seq Scan 反而更快，优化器会正确地忽略索引。上例的价值在于看计划形状：方案 A 只有 Filter，方案 B/C 出现了 Index Scan。数据量上万后差距是数量级的。

&& 的 GiST 索引与多列组合 ​

范围列要发挥性能，必须有 GiST 索引；若查询还带 room_id 之类的等值条件，应建多列 GiST（需 btree_gist）：

sql
CREATE EXTENSION IF NOT EXISTS btree_gist;

-- 只要时间重叠判定
CREATE INDEX idx_period_gist ON room_reservations USING GIST (period);

-- 房间 + 时段组合过滤
CREATE INDEX idx_room_period_gist ON room_reservations USING GIST (room_id, period);
索引加速的操作备注
GiST&& @> <@ = `--`
SP-GiST同上，部分场景更快不支持 = 的排除约束
B-tree只能加速 start_at < ? 这类单端条件无法整体排序范围
无索引OVERLAPS / upper(period) > x函数调用或表达式会阻断索引

实战示例 ​

第 1 步：准备数据 ​

sql
-- 复用 meeting_rooms，并补足到 200 行便于观察计划
INSERT INTO meeting_rooms (room_id, booker, start_at, end_at)
SELECT
  'R' || (1 + (i % 10))::TEXT,
  'user_' || i,
  TIMESTAMP '2026-03-01 08:00' + (i * INTERVAL '37 minutes'),
  TIMESTAMP '2026-03-01 08:00' + (i * INTERVAL '37 minutes') + INTERVAL '45 minutes'
FROM generate_series(1, 200) AS i;

CREATE INDEX idx_rooms_start_end ON meeting_rooms (start_at, end_at);
ANALYZE meeting_rooms;

第 2 步：找出与指定时段冲突的全部预订 ​

sql
-- 业务场景：想订 A101 在 2026-03-01 10:00 到 12:00，先看哪些已占用
SELECT id, room_id, booker, start_at, end_at
FROM meeting_rooms
WHERE room_id = 'A101'
  AND (start_at, end_at)
        OVERLAPS
      (TIMESTAMP '2026-03-01 10:00', TIMESTAMP '2026-03-01 12:00')
ORDER BY start_at;
text
 id | room_id | booker |      start_at       |       end_at
----+---------+--------+---------------------+---------------------
  1 | A101    | 张三   | 2026-03-01 09:00:00 | 2026-03-01 11:00:00

索引列 room_id 上的等值条件先过滤，剩下的行再用 OVERLAPS 做 Filter。这也是混合方案的常规形态：能索引的部分交给索引，OVERLAPS 只负责收尾判定。

第 3 步：找出「所有房间」在此时段的空闲房间 ​

sql
-- 先列出所有房间，再排除被占用的
WITH all_rooms AS (
  SELECT DISTINCT room_id FROM meeting_rooms
),
busy AS (
  SELECT DISTINCT room_id
  FROM meeting_rooms
  WHERE (start_at, end_at)
          OVERLAPS
        (TIMESTAMP '2026-03-01 10:00', TIMESTAMP '2026-03-01 12:00')
)
SELECT a.room_id AS free_room
FROM all_rooms a
LEFT JOIN busy b ON b.room_id = a.room_id
WHERE b.room_id IS NULL
ORDER BY a.room_id;
text
 free_room
-----------
 R1
 R3
 R5
 R7
 R9

第 4 步：用范围列改写为可索引写法 ​

sql
-- 在已有表上就地构造范围（不落库，仅用于查询）
CREATE INDEX idx_rooms_period ON meeting_rooms
  USING GIST (tsrange(start_at, end_at, '[)'));

EXPLAIN (ANALYZE, COSTS OFF)
SELECT room_id, booker, start_at, end_at
FROM meeting_rooms
WHERE tsrange(start_at, end_at, '[)')
        &&
      tsrange(TIMESTAMP '2026-03-01 10:00', TIMESTAMP '2026-03-01 12:00', '[)');
text
                              QUERY PLAN
------------------------------------------------------------------------
 Bitmap Heap Scan on meeting_rooms (actual time=0.021..0.048 rows=4 loops=1)
   Recheck Cond: (tsrange(start_at, end_at, '[)'::text) && '["2026-03-01 10:00:00","2026-03-01 12:00:00")'::tsrange)
   Heap Blocks: exact=2
   ->  Bitmap Index Scan on idx_rooms_period (actual time=0.009..0.009 rows=4 loops=1)
         Index Cond: (tsrange(start_at, end_at, '[)'::text) && '["2026-03-01 10:00:00","2026-03-01 12:00:00")'::tsrange)
 Planning Time: 0.132 ms
 Execution Time: 0.078 ms

表达式索引 GIST (tsrange(start_at, end_at, '[)')) 让没有范围列的旧表也能享受 GiST 索引。代价是索引表达式必须与查询表达式逐字一致（包括 '[)'），否则不会被匹配。

第 5 步：端点相接的两种判定对比 ​

sql
-- 只关心「严格重叠」：用半开区间
SELECT count(*) AS strict_overlap
FROM meeting_rooms
WHERE tsrange(start_at, end_at, '[)')
        &&
      tsrange(TIMESTAMP '2026-03-01 11:00', TIMESTAMP '2026-03-01 13:00', '[)');

-- 把相接也算重叠：用闭区间
SELECT count(*) AS inclusive_overlap
FROM meeting_rooms
WHERE tsrange(start_at, end_at, '[]')
        &&
      tsrange(TIMESTAMP '2026-03-01 11:00', TIMESTAMP '2026-03-01 13:00', '[]');

-- 相邻（首尾相接）单独判定
SELECT count(*) AS adjacent
FROM meeting_rooms
WHERE tsrange(start_at, end_at, '[)')
        -|-
      tsrange(TIMESTAMP '2026-03-01 11:00', TIMESTAMP '2026-03-01 13:00', '[)');
text
 strict_overlap | inclusive_overlap | adjacent
----------------+-------------------+----------
              2 |                 5 |        1

「相邻」用 -|- 而不是 NOT (a && b)：后者把所有不相交的情况（包括相隔一年）都算成真，语义完全不同。

第 6 步：验证排除约束 ​

sql
-- 同一个房间、同一时段插两次，第二次失败
INSERT INTO room_reservations (room_id, booker, start_at, end_at)
VALUES ('B201', '钱七', '2026-03-02 09:00+08', '2026-03-02 10:00+08'),
       ('B201', '孙八', '2026-03-02 09:30+08', '2026-03-02 10:30+08');
-- ERROR:  conflicting key value violates exclusion constraint "no_room_overlap"
sql
-- 正确做法：应用层捕获冲突后重试，或用 ON CONFLICT 无法处理 EXCLUDE，需自行重试
-- 这里演示「先探测再插入」的探测查询
SELECT EXISTS (
  SELECT 1 FROM room_reservations
  WHERE room_id = 'B201'
    AND period && tstzrange(TIMESTAMP '2026-03-02 09:30+08',
                            TIMESTAMP '2026-03-02 10:30+08', '[)')
) AS conflict_exists;
text
 conflict_exists
-----------------
 f

注意事项 ​

  1. OVERLAPS 必须写成行构造器，两侧都要带括号，且每侧恰好两个时间值：
    sql
    -- 正确
(start1, end1) OVERLAPS (start2, end2)
-- 错误！缺少括号会语法错误
start1, end1 OVERLAPS start2, end2
  2. OVERLAPS 是闭区间语义，端点相等判定为重叠；排班、预约业务必须改用半开区间：
    sql
    -- 会把 11:00 结束与 11:00 开始判为重叠
(start_at, end_at) OVERLAPS ('... 11:00', '... 12:00')
-- 推荐
tsrange(start_at, end_at, '[)') && tsrange('... 11:00', '... 12:00', '[)')
  3. 任一参数为 NULL 时返回 NULL 而非 false，WHERE NOT (...) 会静默丢掉这些行：
    sql
    -- 不会返回 NULL 行
WHERE NOT (start_at, end_at) OVERLAPS (?, ?)
-- 需要包含 NULL 行时
WHERE ((start_at, end_at) OVERLAPS (?, ?)) IS NOT TRUE
  4. OVERLAPS 用不了索引，只能作为 Filter 逐行求值。高选择性场景必须改写为 a < d AND c < b 或用范围列 + GiST。
  5. a < d AND c < b 是半开区间的正确改写，不是 a <= d AND c >= b，写成后者就退化成闭区间了。
  6. 范围类型不接受 start > end，会直接报错；而 OVERLAPS 会自动交换。从 OVERLAPS 迁移到范围类型时，先校验数据里没有反向区间：
    sql
    SELECT count(*) FROM t WHERE start_at > end_at;
  7. tsrange / tstzrange 的默认边界是 [)，daterange 也是。只有需要包含结束点时才写 '[]'。
  8. daterange 会自动规范化：'[2026-01-01,2026-01-31]'::daterange 变成 [2026-01-01,2026-02-01)，因此相邻天的判定天然正确。
  9. 多时段（不连续）场景用 multirange（PostgreSQL 14+）：tsmultirange 支持「每天 9-12 点」这类拼装，&& 同样适用。
  10. EXCLUDE USING GIST 里的 = 需要 btree_gist 扩展，否则报 data type text has no default operator class for access method "gist"。
  11. 排除约束不能被 ON CONFLICT 捕获，它抛的是 23P01（exclusion_violation）而不是 23505（唯一冲突）。应用层要按 23P01 做重试或友好提示。
  12. 表达式索引的表达式必须与查询逐字一致。GIST (tsrange(start_at, end_at, '[)')) 与查询里的 tsrange(start_at, end_at, '[)') 大小写、边界符都要吻合。

最佳实践 ​

  1. 新表直接存范围列（tstzrange / tsrange / daterange），不要用两个独立时间戳列再临时构造范围；范围列既可索引又能加约束。
  2. 范围列一律用 [) 半开区间，这是时间段的黄金标准：相邻不重叠、无空隙、语义统一。
  3. 用 GENERATED ALWAYS AS ... STORED 从时间戳派生范围列，避免应用层双写不一致，同时保留两个时间戳列给报表用。
  4. 预约/排班表必须加 EXCLUDE USING GIST 约束，它是并发场景下唯一可靠的防重叠手段：
    sql
    CONSTRAINT no_overlap
  EXCLUDE USING GIST (resource_id WITH =, period WITH &&)
  5. 查询用 && 而不是 OVERLAPS，前者能走 GiST 索引；只有在需要「闭区间语义」或必须使用 SQL 标准语法时才用 OVERLAPS。
  6. 为范围列建 GiST 索引。EXCLUDE 约束会自动建一个，但只覆盖约束用到的列；普通查询要另建：
    sql
    CREATE INDEX idx_period_gist ON bookings USING GIST (period);
  7. 混合过滤时把可索引列放前：WHERE resource_id = ? AND period && ?，配合 GIST (resource_id, period) 多列索引效益最好。
  8. 跨时区业务一律用 tstzrange，tsrange 只在明确不需要时区换算时使用，否则夏令时切换日会产生错判。
  9. 用 -|- 判定相邻，不要用 NOT (a && b) 或 NOT OVERLAPS 代替，语义差别很大。
  10. NULL 时间列在建表时就设计成 NOT NULL，避免让「时间未知」这种状态污染重叠判定；无法避免时在查询里用 IS NOT TRUE 兜底。
  11. 定期核对约束有效性：EXCLUDE 只能防新增数据，若历史数据是用 ALTER TABLE ... ADD CONSTRAINT 后加约束，失败即说明存量数据已重叠，需要先清理。
  12. 历史数据按范围列分区或归档。period && '[now, ...)' 这类查询只关心未来，历史堆积会拖慢索引。

下一节

继续学习：COUNT / SUM / AVG / MAX / MIN

参考链接 ​