16.1 时间语义先于时序扩展

时序系统首先是时间语义系统，其次才是高吞吐写入、压缩或连续聚合系统。

如果一张表只有 created_at，读者无法判断：

它由设备、应用还是数据库生成；
它表示业务发生、消息到达、事务提交还是规则生效；
它能否用于重建业务顺序；
它是否适合成为分区键；
迟到事件应修改旧聚合，还是被丢弃；
修改历史维表后，旧事件是否要重新归属。

本节先建立一套可以写入 schema、查询和验收证据的时间词汇。

16.1.1 事件时间、处理时间与有效时间

一条事实至少可能有四只钟

时间	回答的问题	常见来源
事件时间 `occurred_at`	业务世界何时发生？	设备、业务服务、领域事件
接收时间 `received_at`	本系统何时看见这次尝试？	API/消息消费者入口
处理时间 `processed_at`	某处理阶段何时完成？	worker、ETL、聚合任务
有效时间 `valid_during`	某规则/版本何时适用？	业务配置、主数据版本

接收时间是处理时间的一种边界，但不要因此把所有处理阶段都压成一个字段。例如：

device occurred_at
  -> gateway received_at
  -> queue enqueued_at
  -> consumer processed_at
  -> database committed_at

每一项都可能有诊断价值，却不都应成为业务查询的默认时间。配送事件的“当天发生量”通常按 occurred_at；消息积压通常按 received_at - occurred_at 或 processed_at - received_at；围栏归属还要用 valid_during。

本章的最小模型

setup.sql 创建：

CREATE TABLE shop_ch16.ingest_attempt (
  attempt_id      text PRIMARY KEY,
  event_id        text NOT NULL,
  occurred_at     timestamptz NOT NULL,
  received_at     timestamptz NOT NULL,
  courier_id      text NOT NULL,
  event_type      text NOT NULL,
  longitude       numeric(9,5) NOT NULL,
  latitude        numeric(8,5) NOT NULL,
  source_sequence bigint NOT NULL,
  CHECK (received_at >= occurred_at)
);

CREATE TABLE shop_ch16.event_registry (
  event_id             text PRIMARY KEY,
  canonical_attempt_id text NOT NULL REFERENCES shop_ch16.ingest_attempt,
  first_received_at    timestamptz NOT NULL,
  last_received_at     timestamptz NOT NULL,
  attempt_count        integer NOT NULL,
  payload_fingerprint  text NOT NULL
);

原始尝试与规范事件分开，保留两种真相：

transport truth: 这条消息到过几次、每次何时到
business truth: 这个 event_id 只产生一个领域事实

若直接把 event_id 设为事件表唯一键并使用 ON CONFLICT DO NOTHING，数据库能做到幂等，却会丢失重试次数和 payload 冲突证据。本章先保存 ingest_attempt，再要求同一 event_id 的业务 payload 完全一致，选择最早到达的尝试作为 canonical。

`timestamptz` 表示绝对时刻

PostgreSQL 有两种常被混淆的 timestamp：

类型	语义	是否保留输入时区名称
`timestamp with time zone` / `timestamptz`	时间线上的绝对时刻	否
`timestamp without time zone`	没有时区解释的日期与墙上时间	不适用

timestamptz 输入会依据显式偏移、时区名称或会话 TimeZone 解释成绝对时刻；内部以统一形式保存，输出时再按当前 TimeZone 显示。原始的 America/New_York、Asia/Shanghai 名称不会随值保存。

因此，下面两个输入表示同一个时刻：

SELECT
  TIMESTAMPTZ '2026-03-08 07:05:00+00'
    =
  TIMESTAMPTZ '2026-03-08 03:05:00-04';

结果是 true。若业务还要知道用户选择的法定时区，应另存经过校验的 IANA 时区名：

event_timezone text

不要试图从 UTC offset 反推时区。-04:00 同时可能对应多个地区，也不能表达未来或过去的夏令时规则。

PostgreSQL 官方日期时间类型详细描述输入、存储与输出行为。特别要注意：一个没有偏移的字符串写入 timestamptz 时依赖会话 TimeZone，所以 API 合同应要求显式偏移。

`timestamp` 也有正当用途

不能把规则简化为“永远用 timestamptz”。以下值本来就不是一个已确定的绝对时刻：

商店每天 09:00 开门；
用户生日 1990-05-06；
“2027 年 5 月第一周一上午十点”这项待排程规则；
一张历史文档只记录了当地时间但未知地区。

它们应使用 date、time、timestamp 或“本地日期时间 + IANA 时区 + 解析状态”的复合模型。只有在规则被具体化到某个地区和日期后，才能解析出 timestamptz。

有效时间是区间，不是两个互不相关字段

本章围栏版本：

CREATE TABLE shop_ch16.geofence_version (
  zone_id      text NOT NULL,
  version      integer NOT NULL,
  valid_during tstzrange NOT NULL,
  zone_geom    geometry(Polygon, 4326) NOT NULL,
  PRIMARY KEY (zone_id, version)
);

与 valid_from、valid_to 两列相比，range 把“是否包含端点、是否为空、是否重叠”变成类型和操作符可见的合同：

valid_during @> event.occurred_at
valid_during && another_range
lower(valid_during)
upper(valid_during)

业务连接因而直接表达为：

JOIN shop_ch16.geofence_version AS zone
  ON zone.valid_during @> event.occurred_at

这回答的是“事件发生时哪个版本有效”，而不是“现在最新版本是什么”。

事务时间是另一条轴

本章没有实现完整双时态表。现实中还可能需要：

valid time: 业务上何时有效
system time: 数据库何时知道/记录这个版本

例如 3 月 10 日才补录“围栏从 3 月 8 日开始生效”，业务有效期与系统记录期不同。若需要审计“当时系统认为什么”，应增加系统版本、审计表或不可变事件日志，而不是覆盖旧行后只保留最终答案。

选择默认时间的判断表

问题	应优先使用
某日发生多少配送事件	`occurred_at`
消息积压/链路延迟	`received_at - occurred_at`
worker 吞吐与处理延迟	`processed_at - received_at`
历史事件属于哪个围栏	`valid_during @> occurred_at`
何时把修订写入数据库	审计/事务时间
数据保留按到达还是发生	由法规和回补合同明确，不能猜

一张表可以有多只钟，但每个查询只能在合同中明确自己使用哪一只。

16.1.2 时区、迟到、乱序与重复事件

时区是显示规则，也是输入解析规则

本章连接上下文固定：

SET TimeZone = 'UTC';
SET DateStyle = 'ISO, YMD';

这让导出和校验和稳定，不意味着用户只能看 UTC。展示时显式转换：

SELECT
  event_id,
  occurred_at,
  occurred_at AT TIME ZONE 'America/New_York' AS local_time
FROM shop_ch16.delivery_event
WHERE event_id IN ('e002', 'e003')
ORDER BY occurred_at;

AT TIME ZONE 的返回类型取决于输入：

timestamptz AT TIME ZONE zone -> timestamp
timestamp   AT TIME ZONE zone -> timestamptz

前者把绝对时刻投影为某地墙上时间；后者把无时区的墙上时间按指定地区解释为绝对时刻。方向相反，代码审查时必须看输入类型，不能只看函数名字。

夏令时反例

fixture 中：

e002 = 2026-03-08 06:55:00+00
e003 = 2026-03-08 07:05:00+00

纽约本地显示：

e002 = 2026-03-08 01:55:00
e003 = 2026-03-08 03:05:00

正确的实际时长：

SELECT e3.occurred_at - e2.occurred_at;
-- 00:10:00

错误模式是先把两边转成无时区本地时间再相减：

SELECT
  (e3.occurred_at AT TIME ZONE 'America/New_York')
  -
  (e2.occurred_at AT TIME ZONE 'America/New_York');
-- 01:10:00

第二个结果计算的是墙上刻度差，不是经过时长。在秋季回拨时还可能出现同一本地时刻两次。绝对持续时间应在 timestamptz 上运算；按本地日历排程则要先明确地区，再接受 DST 带来的 23/25 小时日。

temporal-analysis.sql 将两个本地显示与 600 秒实际差同时固化，防止只验证其中一面。

迟到不等于乱序

本章定义超过五分钟为“迟到”：

received_at - occurred_at > interval '5 minutes'

固定迟到事件：

e001 delay = 29100 seconds
e004 delay =   900 seconds

迟到比较事件时间与接收/处理时间。乱序比较多个事件在两条时间轴上的顺序。本章：

e004 occurred 11:55, received 12:10
e005 occurred 12:00, received 12:00:05

e004 先发生却后到达，因此 e004 -> e005 是乱序对。一个事件可以迟到但不造成当前批次乱序，也可以只晚几秒却翻转相邻事件顺序。

迟到策略不能藏在 SQL 里

流式或增量聚合常见策略：

策略	好处	代价
永远接受并重算	最接近最终事实	旧分区、缓存和下游长期可变
水位线内重算	成本可控	水位线外需要补偿路径
迟到旁路/人工处理	主链稳定	两套状态与操作流程
直接丢弃	简单	数据损失，必须有明确业务授权

水位线不是 now() - interval '5 minutes' 这么简单。还要定义：

以哪个来源、分区或租户推进；
空闲来源如何处理；
来源时钟漂移多大；
重放是否让水位线倒退；
聚合、缓存、物化视图和外部消费者如何更正；
超过水位线的数据被保留、补偿还是拒绝。

本章只标记迟到，不模拟完整流处理平台。它建立的是数据库中可重算的事实基础。

重复也有两种

传输重复：同一 event_id、同一 payload 被发送多次。本章 e003 有 a003/a004 两次尝试，注册表记录：

event_id=e003
canonical_attempt_id=a003
attempt_count=2

业务冲突：同一 event_id 带来不同 payload。不能将它静默视为普通重复。本章 loader 先计算 payload variant 数，只有恰好一个版本的 event_id 才进入注册表；生产应把冲突写入隔离表并报警。

一个可靠幂等键应来自领域身份，而不是接收时间或随机重试 ID：

good: order_id + event_type + domain sequence
risky: received_at
risky: database-generated serial for every retry

若来源只能提供“近似重复”，需要另设去重窗口、payload hash 与误合并风险，不能假装获得 exactly-once。

来源序列补足时间排序

两条事件可能具有相同 timestamp 精度，设备时钟也可能回拨。本章保留：

source_sequence bigint NOT NULL

同一 courier 内的业务顺序可用：

ORDER BY courier_id, source_sequence, event_id

它不替代时间：序列通常只能在单一来源内比较，也无法回答真实时长。稳健模型同时保存领域序列、事件时间、接收时间和唯一身份。

输入时钟也要被观测

本章约束 received_at >= occurred_at 是教学简化。生产设备的时钟可能快于服务器，直接拒绝会丢数据。更现实的处理是：

source_occurred_at
server_received_at
clock_skew_estimate
normalized_occurred_at (optional and versioned)
source clock quality/status

原始时间不可覆盖；任何校正都要带算法版本，才能在规则变化后重算。

16.1.3 范围类型、窗口与时间对齐

半开区间让相邻边界只有一个归属

本章统一采用：

[lower, upper)

左端包含，右端不包含。因此：

central v1 [00:00, 12:00)
central v2 [12:00, next_day)

正好 12:00 只属于 v2。日分区：

day8 [2026-03-08 00:00Z, 2026-03-09 00:00Z)
day9 [2026-03-09 00:00Z, 2026-03-10 00:00Z)

23:59:59 与次日 00:00:00 也不会重叠或漏掉。不要用 23:59:59.999999 人工制造闭区间上界：精度变化、类型转换和代码生成很容易产生缝隙。

PostgreSQL range 支持包含、重叠、相邻、交集等操作，并可由 GiST/SP-GiST 索引。参见官方 Range Types。

用约束保护有效期

同一围栏版本不能重叠：

EXCLUDE USING gist (
  zone_id      shop_ch16_ext.gist_text_ops WITH =,
  valid_during WITH &&
);

普通 B-tree 能找 zone_id，却不能单独表达“同 zone 的两个 range 不得重叠”。btree_gist 为标量等值提供 GiST operator class，使它能与 range 重叠操作符组合在一个排他约束中。

故意写入：

central 99 [2026-03-08 11:00Z, 13:00Z)

会与 v1/v2 冲突并返回 23P01。这比在应用中先 SELECT 再 INSERT 可靠，因为并发事务仍由数据库约束仲裁。

时间桶必须固定原点

本章用原生 date_bin 对齐 15 分钟：

SELECT
  date_bin(
    interval '15 minutes',
    occurred_at,
    timestamptz '2001-01-01 00:00:00+00'
  ) AS bucket_start,
  count(*)
FROM shop_ch16.delivery_event
GROUP BY bucket_start;

三个参数分别是：

stride   桶宽
source   待对齐时间
origin   网格原点

不固定 origin，就没有完整的桶合同。不同服务若使用不同原点，即使桶宽相同也无法合并。

date_trunc('hour', ...) 适合自然日历单位；date_bin 可表达 15 分钟这类任意固定长度，但不能把“一个月”当成固定秒数。月份、季度、当地营业日应使用明确日历与时区规则。

官方 Date/Time Functions 给出 date_trunc、date_bin 与 AT TIME ZONE 的类型和行为。

UTC 桶与本地日历桶不是同一产品

UTC 15 分钟监控桶：

date_bin('15 minutes', occurred_at, '2001-01-01Z')

“纽约当地营业日”则需要先定义本地日期边界，再转换成两个绝对时刻作为查询范围。不要简单写：

(occurred_at AT TIME ZONE 'America/New_York')::date = :day

这虽然逻辑可读，却可能包裹分区键而失去裁剪。更好的应用流程：

input local date + IANA zone
  -> resolve local midnight and next local midnight
  -> obtain two timestamptz bounds
  -> query occurred_at >= lower AND occurred_at < upper

DST 切换日的两个 UTC 边界可能相差 23 或 25 小时，这恰好是正确的当地日。

窗口函数不是时间窗口状态机

SQL 窗口函数：

lag(occurred_at) OVER (
  PARTITION BY courier_id
  ORDER BY occurred_at, event_id
)

能在当前查询快照中比较相邻事件，适合轨迹间隔、停留候选和乱序审计。但它不会自动：

等待迟到事件；
维护跨批水位线；
修正已发送给外部系统的结果；
把无限事件流变成有界状态。

数据库增量表、物化视图、TimescaleDB continuous aggregate 或外部流系统可以承接不同职责。选择前仍要先固定 event time、lateness 与 correction 合同。

可执行验收

运行：

psql "service=pg36-admin" \
  -f static/labs/ch16/temporal-analysis.sql

psql "service=pg36-admin" \
  -f static/labs/ch16/time-buckets.sql

关键事实：

duplicate_event=e003:2
late_event_ids=e001,e004
out_of_order_pair=e004->e005
utc_day8_events=7
partition_boundary=e008=...20260308;e009=...20260309

时间桶共 11 个，事件总数仍为 12，迟到总数仍为 2；12:00 桶含 e005/e006 两行。聚合后的总量与原始事实不守恒时，应先定位过滤、边界或重复处理，而不是调整索引。

本节判断线

进入下一节前，至少能完整回答：

哪一列是 event time？
哪一列是 ingest/processing time？
业务规则如何表示 valid time？
输入没有 offset 时由谁解释？
绝对时长在哪种类型上计算？
迟到阈值与水位线是什么？
重复 payload 冲突如何处理？
所有相邻区间采用什么端点合同？
本地日如何转换成可裁剪的绝对范围？

回答不完整时，不应先争论日分区还是小时分区，也不应先安装时序扩展。

返回本章目录 · 下一节：时序表与时间分区 · 查看全书目录 · 查看索引中心

最后更新于 2026-07-30

16.2 时序表与时间分区