PostgreSQL / 系统课时
日期与时间类型
日期与时间类型
当数据库需要记录生日、营业时间、订单发生时刻或服务时长时,应根据数据含义选择合适的时间类型。本课通过 PostgreSQL 的类型字面量和日期加减运算,掌握常用时间类型的用途,并理解时区与自然月计算的边界。
学习目标
- 区分日期、时间、时间戳和时间间隔
- 使用类型字面量编写明确的时间 SQL,并验证运算结果
- 根据业务场景选择时间类型,识别时区和月份运算的边界
四类时间数据
date 只保存年月日,适合生日、账期和自然日。例如,用户生日为 2026-08-01,不需要记录具体时分秒。
time without time zone(通常简写为 time)只保存一天中的时分秒,适合营业开始时间。例如,每天 09:30:00 开门,但它不能单独表示具体哪一天,也不包含时区信息。
timestamp without time zone(通常简写为 timestamp)保存日期和时间的组合,不进行时区换算。例如,门店预约时间为 2026-08-01 09:30:00,表示当地日历上的一个日期和时刻,本身不代表具有全球唯一含义的时间点。
interval 保存一段时间间隔,如 2 天、3 小时或 1 个月,可用于时间加减。它可以包含月份、天数和小时等不同单位,不应简单理解为一个固定的秒数。
这四类数据解决不同问题:生日使用 date,每天固定的营业时间使用 time,不带时区的预约时间使用 timestamp,任务持续时间或账期跨度使用 interval。
使用固定值进行运算
时间 SQL 应尽量写出类型前缀,避免字符串被隐式转换后产生歧义。PostgreSQL 可以通过 DATE、TIME、TIMESTAMP 和 INTERVAL 构造明确类型的字面量。
下面示例不依赖当前时间,因此每次执行得到的日期与时间值一致。将 SQL 复制到 psql 或 PostgreSQL 查询工具执行即可。
SELECT
DATE '2026-08-01' AS start_date,
TIME '09:30:00' AS start_time,
TIMESTAMP '2026-08-01 09:30:00' AS start_at,
INTERVAL '2 days 3 hours' AS duration,
DATE '2026-08-01' + INTERVAL '2 days 3 hours' AS finish_at;预期结果(默认时间间隔显示格式)
start_date | start_time | start_at | duration | finish_at
------------+------------+---------------------+-----------------+---------------------
2026-08-01 | 09:30:00 | 2026-08-01 09:30:00 | 2 days 03:00:00 | 2026-08-03 03:00:00
(1 row)这里需要注意,DATE '2026-08-01' 只有日期,但与包含小时的 interval 相加时,PostgreSQL 会以当天 00:00:00 为起点计算,并返回 timestamp 类型。因此,加上 2 天 3 小时后,结果是 2026-08-03 03:00:00,而不是 2026-08-03 12:30:00。示例中的 start_time 和 start_at 是独立展示的值,并未参与 finish_at 的计算。
验证时重点核对三个值:开始日期为 2026-08-01,间隔为 2 天 3 小时,结束时间为 2026-08-03 03:00:00。如果客户端的输出排版或间隔显示格式不同,应比较实际时间值,而不是空格数量。
时间点与时区
如果业务需要记录订单支付、消息发送等真实发生的时间点,应考虑 timestamp with time zone,通常简写为 timestamptz。它与不带时区的 timestamp 有重要区别。
timestamp 仅记录日历日期和时钟时间。即使输入文本包含时区偏移量,只要明确转换为 timestamp without time zone,该偏移量也不会用于时区换算。因此,它适合需要保留本地日期时间、并由业务另外管理所属时区的场景。
timestamptz 则代表可跨时区比较的实际时间点。PostgreSQL 根据输入中的时区偏移量,或未指定偏移量时的会话时区,确定具体时间点;查询显示时再按当前会话的 TimeZone 转换。它并不会保留原始输入的时区名称。
例如,北京时间 2026 年 8 月 1 日 09:30 与 UTC 时间同日 01:30 表示同一个时间点。将这类数据存为 timestamptz,便于跨地区排序、比较和计算。若业务还需要保留用户选择的时区,应另外存储时区标识,例如 Asia/Shanghai。
选择与边界
仅表示自然日时不要使用时间戳,否则会引入无意义的时分秒。仅表示持续时长时,不要使用普通整数保存含义不明的数值;如果单位已经固定,例如 duration_seconds,使用整数也是合理选择。
日期运算还需要区分固定天数和自然月份。interval 中的月份不是固定 30 天。例如,从 2026 年 1 月 31 日加 1 个自然月,在 PostgreSQL 中会得到 2026 年 2 月 28 日;而加 30 天得到的是 2026 年 3 月 2 日。账期计算应先明确采用自然月规则还是固定天数规则。
跨夏令时切换时,对 timestamptz 增加 INTERVAL '1 day' 和增加 INTERVAL '24 hours' 也可能得到不同的本地时钟结果,因为前者按日历日推进,后者表示固定 24 小时。
实际设计表结构时,可以先判断字段记录的是日历日期、每天的钟点、带日期的本地时刻、真实时间点,还是持续间隔,再分别选择 date、time、timestamp、timestamptz 或 interval。涉及跨时区业务时,应同时明确输入时区、数据库会话时区以及客户端的显示规则。