PostgreSQL / 系统课时

数值、时间与时区类型

数值、时间与时区类型

本课解决“字段看似能存,为什么计算和时间展示仍会出错”的问题。你将根据金额、测量值、业务日期和事件时间的真实语义选择 PostgreSQL 类型,并用可执行 SQL 验证精度及时区转换结果。

学习目标

  • 区分精确数值、近似数值和整数类型的适用边界
  • 正确选择 date、time、timestamp 与 timestamptz
  • 设计可跨时区存储、查询和展示的时间字段

数值类型按业务语义选择

金额、费率、税额等必须精确计算的数据,应使用 numeric(p, s);计数、状态码和序号应优先使用整数类型;科学计算、传感器读数和可容忍舍入误差的统计值可以使用 real 或 double precision。

numeric(12, 2) 表示最多 12 位有效数字,其中 2 位在小数点后。它能准确保存十进制值,但计算和存储成本高于整数和浮点数。高吞吐支付系统也可把最小货币单位保存为 bigint,例如用分而不是元,但必须统一币种精度和换算规则。

下面的查询展示十进制精确计算与二进制浮点计算的差异。

sql
SELECT
    0.1::numeric + 0.2::numeric AS exact_sum,
    0.1::double precision + 0.2::double precision AS approximate_sum;

预期输出:

text
exact_sum | approximate_sum
----------+---------------------
0.3       | 0.30000000000000004

因此,金额字段不要使用 double precision。比较浮点结果时也不应直接依赖完全相等,而应结合业务允许的误差范围。

时间类型表达不同事实

date 只表达日历日期,适合账期、生日和营业日;time 只表达一天中的时间,适合固定营业时段;timestamp without time zone 表达“没有时区语义的本地日期时间”;timestamptz 表达全球时间线上的一个确定瞬间。

PostgreSQL 的 timestamptz 不会永久保存输入中的时区名称。它会把时刻规范化存储,并按当前会话的 TimeZone 转换后显示。也就是说,同一条数据在吉隆坡和 UTC 会显示不同钟表时间,但代表的是同一个瞬间。

sql
SET TIME ZONE 'Asia/Kuala_Lumpur';

SELECT TIMESTAMPTZ '2026-08-03 10:00:00+08' AS malaysia_view;

SET TIME ZONE 'UTC';

SELECT TIMESTAMPTZ '2026-08-03 10:00:00+08' AS utc_view;

预期输出:

text
SET
malaysia_view
------------------------
2026-08-03 10:00:00+08

SET
utc_view
------------------------
2026-08-03 02:00:00+00

两个结果相差 8 小时的显示偏移,但底层时刻相同。事件创建时间、支付完成时间、审计时间和日志时间通常都应使用 timestamptz。

建模一个可验证的业务表

下面的表同时保存订单金额、业务日期、门店营业时间和实际发生时刻。字段名称直接表达业务语义,可减少后续开发中的错误转换。

sql
DROP TABLE IF EXISTS payment_event_demo;

CREATE TABLE payment_event_demo (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    amount numeric(12, 2) NOT NULL CHECK (amount >= 0),
    business_date date NOT NULL,
    store_open_time time NOT NULL,
    occurred_at timestamptz NOT NULL
);

SET TIME ZONE 'Asia/Kuala_Lumpur';

INSERT INTO payment_event_demo (
    amount,
    business_date,
    store_open_time,
    occurred_at
) VALUES (
    128.50,
    DATE '2026-08-03',
    TIME '09:00:00',
    TIMESTAMPTZ '2026-08-03 10:15:00+08'
);

SELECT id, amount, business_date, store_open_time, occurred_at
FROM payment_event_demo;

预期输出:

text
DROP TABLE
CREATE TABLE
SET
INSERT 0 1
id | amount | business_date | store_open_time | occurred_at
---+--------+---------------+-----------------+------------------------
1  | 128.50 | 2026-08-03    | 09:00:00        | 2026-08-03 10:15:00+08

business_date 与 occurred_at 不可互相替代。夜间营业、跨午夜结算或按门店当地日期统计时,业务日期可能由规则计算,而不是简单截取事件时间的日期部分。

时区转换与查询边界

向客户端返回时间时,推荐传输 ISO 8601 格式并包含偏移量,例如 2026-08-03T10:15:00+08:00。服务端内部可以统一使用 UTC,但展示和按本地日期分组时必须明确目标时区。

将 timestamptz 转为某地本地时间可使用 AT TIME ZONE。结果是无时区的 timestamp,适合展示或计算当地日期,不应误当成新的全球时刻再次入库。

sql
SET TIME ZONE 'UTC';

SELECT
    occurred_at,
    occurred_at AT TIME ZONE 'Asia/Kuala_Lumpur' AS malaysia_local_time
FROM payment_event_demo;

预期输出:

text
SET
occurred_at              | malaysia_local_time
-------------------------+---------------------
2026-08-03 02:15:00+00   | 2026-08-03 10:15:00

查询某地某一天的事件时,最佳做法是先把当地日界线转换成两个 timestamptz 边界,再使用半开区间 >= start AND < end。不要对索引列直接调用日期转换函数,否则可能影响普通 B-tree 索引的使用。

常见错误与约束

不要使用 timestamp without time zone 保存跨地区事件,也不要把客户端未带偏移量的时间字符串直接解释为全球时刻。夏令时地区还会出现重复或不存在的本地时间,必须结合 IANA 时区名称进行解析。

类型约束应尽量靠近数据。金额可使用 CHECK 限制范围,必填事件时间应设置 NOT NULL,默认当前时刻可使用 DEFAULT now()。now() 在同一事务内保持不变;若需要语句执行时的实时值,应理解 statement_timestamp() 和 clock_timestamp() 的差异后再选择。

本课总结

  • 金额等十进制精确数据使用 numeric 或统一最小货币单位的整数,不使用浮点数
  • 全球事件时刻使用 timestamptz,业务日期和固定营业时间分别使用 date 与 time
  • 时区转换应明确目标地区,并用半开时间区间查询,避免对索引列做逐行函数转换

下一课预告

下一课将继续讨论数据建模中的复杂类型选择,重点分析数组、范围或复合结构在约束、查询和索引方面的取舍。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

数值、时间与时区类型

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通