PostgreSQL / 系统课时
数值、时间与时区类型
数值、时间与时区类型
本课解决“字段看似能存,为什么计算和时间展示仍会出错”的问题。你将根据金额、测量值、业务日期和事件时间的真实语义选择 PostgreSQL 类型,并用可执行 SQL 验证精度及时区转换结果。
学习目标
- 区分精确数值、近似数值和整数类型的适用边界
- 正确选择
date、time、timestamp与timestamptz - 设计可跨时区存储、查询和展示的时间字段
数值类型按业务语义选择
金额、费率、税额等必须精确计算的数据,应使用 numeric(p, s);计数、状态码和序号应优先使用整数类型;科学计算、传感器读数和可容忍舍入误差的统计值可以使用 real 或 double precision。
numeric(12, 2) 表示最多 12 位有效数字,其中 2 位在小数点后。它能准确保存十进制值,但计算和存储成本高于整数和浮点数。高吞吐支付系统也可把最小货币单位保存为 bigint,例如用分而不是元,但必须统一币种精度和换算规则。
下面的查询展示十进制精确计算与二进制浮点计算的差异。
SELECT
0.1::numeric + 0.2::numeric AS exact_sum,
0.1::double precision + 0.2::double precision AS approximate_sum;预期输出:
exact_sum | approximate_sum
----------+---------------------
0.3 | 0.30000000000000004因此,金额字段不要使用 double precision。比较浮点结果时也不应直接依赖完全相等,而应结合业务允许的误差范围。
时间类型表达不同事实
date 只表达日历日期,适合账期、生日和营业日;time 只表达一天中的时间,适合固定营业时段;timestamp without time zone 表达“没有时区语义的本地日期时间”;timestamptz 表达全球时间线上的一个确定瞬间。
PostgreSQL 的 timestamptz 不会永久保存输入中的时区名称。它会把时刻规范化存储,并按当前会话的 TimeZone 转换后显示。也就是说,同一条数据在吉隆坡和 UTC 会显示不同钟表时间,但代表的是同一个瞬间。
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;预期输出:
SET
malaysia_view
------------------------
2026-08-03 10:00:00+08
SET
utc_view
------------------------
2026-08-03 02:00:00+00两个结果相差 8 小时的显示偏移,但底层时刻相同。事件创建时间、支付完成时间、审计时间和日志时间通常都应使用 timestamptz。
建模一个可验证的业务表
下面的表同时保存订单金额、业务日期、门店营业时间和实际发生时刻。字段名称直接表达业务语义,可减少后续开发中的错误转换。
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;预期输出:
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+08business_date 与 occurred_at 不可互相替代。夜间营业、跨午夜结算或按门店当地日期统计时,业务日期可能由规则计算,而不是简单截取事件时间的日期部分。
时区转换与查询边界
向客户端返回时间时,推荐传输 ISO 8601 格式并包含偏移量,例如 2026-08-03T10:15:00+08:00。服务端内部可以统一使用 UTC,但展示和按本地日期分组时必须明确目标时区。
将 timestamptz 转为某地本地时间可使用 AT TIME ZONE。结果是无时区的 timestamp,适合展示或计算当地日期,不应误当成新的全球时刻再次入库。
SET TIME ZONE 'UTC';
SELECT
occurred_at,
occurred_at AT TIME ZONE 'Asia/Kuala_Lumpur' AS malaysia_local_time
FROM payment_event_demo;预期输出:
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 - 时区转换应明确目标地区,并用半开时间区间查询,避免对索引列做逐行函数转换
下一课预告
下一课将继续讨论数据建模中的复杂类型选择,重点分析数组、范围或复合结构在约束、查询和索引方面的取舍。