INTERVAL 时间间隔
本节讲解 PostgreSQL 的 INTERVAL 类型。你将掌握它的输入格式、内部存储结构、日期加减运算、justify_* 系列函数的作用，以及月末、闰年等边界行为。
前置知识
阅读本节前，建议先了解：TIMESTAMPTZ 详解
基础概念
时间间隔（Interval）表示一段"时长"，而不是时间轴上的一个点。它与 DATE / TIMESTAMP 的关系，正如"距离"与"坐标"的关系。
INTERVAL 与其他时间类型最大的不同在于：它由三个独立字段组成，而不是一个单一数值。
| 内部字段 | 类型 | 含义 | 说明 |
|---|---|---|---|
month | int32 | 月数 | 4 字节，包含年（1 年 = 12 月） |
day | int32 | 天数 | 4 字节，不包含月 |
time | int64 | 微秒数 | 8 字节，包含时/分/秒 |
总存储空间 16 字节。三个字段分开存储是 INTERVAL 最重要的设计特征，因为它导致：
sql
-- 1 个月在 30 天和 31 天的月份里含义不同，所以不能换算成固定天数
SELECT
(INTERVAL '1 month') = (INTERVAL '30 days') AS month_eq_30d,
(INTERVAL '1 day') = (INTERVAL '24 hours') AS day_eq_24h,
(INTERVAL '1 hour') = (INTERVAL '60 minutes') AS hour_eq_60m;text
month_eq_30d | day_eq_24h | hour_eq_60m
--------------+------------+-------------
f | t | t关键术语：
- 时间间隔（Interval）：
INTERVAL，表示时长，符号可正可负。 - 合法化（Justify）：把"超额的分钟/小时/天"向上归并的规范化操作。
- 标度（Scale）：秒的小数位数精度
p，范围 0~6，默认 6。 - 墙上时钟差（Wall Clock Difference）：按字段直接相减得到的差值，不做天数换算。
INTERVAL 支持负值，且三个字段可以各自为负，这会造成反直觉的结果：
sql
SELECT
INTERVAL '-1 month +30 days' AS mixed,
INTERVAL '1 month -30 days' AS mixed2,
(INTERVAL '-1 month +30 days') = (INTERVAL '1 month -30 days') AS equal;text
mixed | mixed2 | equal
------------------+------------------+-------
-1 mons +30 days | 1 mon -30 days | f语法与用法
text
INTERVAL [ fields ] [ (p) ]两种声明方式：
sql
-- 方式一：类型化字面量（推荐，含义明确）
SELECT INTERVAL '1 day 2 hours 3 minutes' AS iv1;
-- 方式二：字符串隐式转换为 interval
SELECT '1 day'::INTERVAL AS iv2;
-- 方式三：带字段限定的标准写法
SELECT INTERVAL '5' DAY AS iv3, INTERVAL '3' HOUR AS iv4;fields 用于限定解释方式，取值如下：
fields 取值 | 说明 | 示例 |
|---|---|---|
| （省略） | 按字符串内容自动解析 | INTERVAL '1-2' → 1 年 2 月 |
YEAR | 只取年 | INTERVAL '2' YEAR |
MONTH | 只取月 | INTERVAL '2' MONTH |
DAY | 只取天 | INTERVAL '2' DAY |
HOUR | 只取小时 | INTERVAL '2' HOUR |
MINUTE | 只取分钟 | INTERVAL '2' MINUTE |
SECOND | 只取秒 | INTERVAL '2' SECOND |
YEAR TO MONTH | 年+月 | INTERVAL '2-6' YEAR TO MONTH |
DAY TO HOUR | 天+小时 | INTERVAL '3 12' DAY TO HOUR |
DAY TO SECOND | 天+时分秒 | INTERVAL '3 12:30:15' DAY TO SECOND |
HOUR TO MINUTE | 时+分 | INTERVAL '3:15' HOUR TO MINUTE |
MINUTE TO SECOND | 分+秒 | INTERVAL '3:15.5' MINUTE TO SECOND |
最简可运行示例：
sql
CREATE TABLE subscriptions (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
plan TEXT NOT NULL,
period INTERVAL NOT NULL,
started_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
INSERT INTO subscriptions (plan, period) VALUES
('monthly', INTERVAL '1 month'),
('quarterly', INTERVAL '3 months'),
('yearly', INTERVAL '1 year');
-- 用间隔直接推算到期时间
SELECT plan, period, started_at + period AS expires_at
FROM subscriptions
ORDER BY id;详细说明
多种输入格式
INTERVAL 的输入解析器非常宽松，支持多种写法：
sql
SELECT
INTERVAL '1 day' AS spaced,
INTERVAL '1day' AS nospace,
INTERVAL '1 d' AS abbrev,
INTERVAL '02:30:00' AS time_only, -- 2 小时 30 分
INTERVAL '1 02:30:00' AS day_time, -- 1 天 2 小时 30 分
INTERVAL '1-6' AS year_month, -- 1 年 6 月
INTERVAL 'P1Y6M' AS iso8601, -- ISO 8601 格式
INTERVAL 'PT2H30M' AS iso_time,
INTERVAL '1 year 2 mons 3 days 04:05:06' AS verbose;text
spaced | nospace | abbrev | time_only | day_time | year_month | iso8601 | iso_time | verbose
------------+-----------+-----------+-----------+--------------------+------------+---------+----------+---------------------
1 day | 1 day | 1 day | 02:30:00 | 1 day 02:30:00 | 1 year 6 mons | 1 year 6 mons | 02:30:00 | 1 year 2 mons 3 days 04:05:06推荐优先使用带单位的英文全称写法（1 day 2 hours），它不受 IntervalStyle 与会话参数影响，可读性最好。
单位与等价关系
| 输入单位 | 归入字段 | 说明 |
|---|---|---|
microsecond / us | time | 微秒 |
millisecond / ms | time | 毫秒 |
second / s | time | 60 s = 1 min |
minute / m / min | time | 60 min = 1 h |
hour / h | time | 24 h = 1 day（存储时不会自动折算到 day） |
day / d | day | 不折算到 month |
week / w | day | 7 days |
month / mon | month | 不折算到 year（12 mon 仍存 12） |
year / y | month | 1 year = 12 months |
decade / century / millennium | month | 10 / 100 / 1000 年 |
关键点：单位不会自动向上归并，这正是 justify_* 函数存在的原因。
sql
-- 输入 25 小时，存储的就是时间字段 90000 秒，不会变成 1 天 1 小时
SELECT
INTERVAL '25 hours' AS raw_25h,
justify_hours(INTERVAL '25 hours') AS justified_25h,
INTERVAL '35 days' AS raw_35d,
justify_days(INTERVAL '35 days') AS justified_35d;text
raw_25h | justified_25h | raw_35d | justified_35d
---------------+-------------------+-----------+---------------
25:00:00 | 1 day 01:00:00 | 35 days | 1 mon 5 days精度：小数秒
INTERVAL 支持秒的小数位精度 p（0~6），声明精度会四舍五入：
sql
SELECT
INTERVAL(0) '1.567 seconds' AS p0,
INTERVAL(3) '1.5678 seconds' AS p3,
INTERVAL(6) '1.567891 seconds' AS p6;text
p0 | p3 | p6
-----------+-----------+---------------
00:00:02 | 00:00:01.568 | 00:00:01.567891注意 p=0 时 1.567 秒被舍入为 2 秒。日常使用可以不写精度，默认 6 位已足够。
日期加减运算
INTERVAL 最常见的用途是与日期时间类型运算：
| 运算 | 结果类型 | 说明 |
|---|---|---|
DATE + INTERVAL | TIMESTAMP | 注意结果升级为 timestamp |
TIMESTAMP ± INTERVAL | TIMESTAMP | 保留原类型 |
TIMESTAMPTZ ± INTERVAL | TIMESTAMPTZ | 保留时区语义 |
TIME ± INTERVAL | TIME | 结果取模 24 小时 |
INTERVAL + INTERVAL | INTERVAL | 字段分别相加 |
INTERVAL * NUMBER | INTERVAL | 各字段分别缩放 |
INTERVAL / NUMBER | INTERVAL | 各字段分别相除 |
sql
SELECT
DATE '2026-01-31' + INTERVAL '1 month' AS date_plus_month,
TIMESTAMP '2026-09-22 10:30:00' + INTERVAL '90 minutes' AS plus_90m,
TIMESTAMPTZ '2026-09-22 10:30:00+08' + INTERVAL '1 day' AS tstz_plus_1d,
TIME '23:30:00' + INTERVAL '2 hours' AS time_wrap,
INTERVAL '1 day' + INTERVAL '5 hours' AS iv_sum,
INTERVAL '1 hour' * 2.5 AS iv_scaled;text
date_plus_month | plus_90m | tstz_plus_1d | time_wrap | iv_sum | iv_scaled
-----------------+---------------------+------------------------+-----------+-------------+-----------
2026-02-28 | 2026-09-22 12:00:00 | 2026-09-23 10:30:00+08 | 01:30:00 | 1 day 05:00:00 | 02:30:00DATE '2026-01-31' + INTERVAL '1 month' 得到 2026-02-28——因为 2 月没有 31 日，会自动截断到当月最后一天（这就是后文要讲的月末边界行为）。
justify_* 系列函数
| 函数 | 作用 | 示例输入 → 输出 |
|---|---|---|
justify_days(iv) | 每 30 天折算为 1 月 | 35 days → 1 mon 5 days |
justify_hours(iv) | 每 24 小时折算为 1 天 | 25:00:00 → 1 day 01:00:00 |
justify_interval(iv) | 先 justify_hours，再 justify_days，并把符号统一 | -1 mon +30 days → 1 mon |
sql
SELECT
justify_days(INTERVAL '60 days') AS jd,
justify_hours(INTERVAL '50 hours') AS jh,
justify_interval(INTERVAL '60 days 50 hours') AS ji;text
jd | jh | ji
------------+-----------+-------------------------------
2 mons | 2 days 02:00:00 | 2 mons 2 days 02:00:00justify_interval 与分步调用的差别在于它还会把混合符号规范化：
sql
SELECT
INTERVAL '-1 month +30 days' AS raw,
justify_hours(justify_days(INTERVAL '-1 month +30 days')) AS step_by_step,
justify_interval(INTERVAL '-1 month +30 days') AS one_shot;text
raw | step_by_step | one_shot
-------------------+--------------------+----------
-1 mons +30 days | 1 mon | 1 mon需要注意：justify_* 只改变表示形式，不改变实际时长语义（30 天在日历上仍被当作 1 个月）。所以对"跨越不同长度月份"的间隔做合法化后，再与日期相加，可能与不合法化时结果相同（也可能不同），不要用它来做业务精度换算。
age() 与 date_trunc
| 函数 | 作用 | 示例 |
|---|---|---|
age(a, b) | 按"日历字段"求 a - b 的年月日间隔 | age('2026-09-22', '1990-03-15') → 36 years 6 mons 7 days |
age(ts) | 等价于 age(current_date, ts) | 常用于计算年龄 |
date_trunc(field, ts) | 把时间戳截断到指定精度 | date_trunc('month', now()) |
sql
SELECT
age(TIMESTAMP '2026-09-22', TIMESTAMP '1990-03-15') AS exact_age,
age(DATE '2026-09-22', DATE '2026-02-28') AS month_diff,
TIMESTAMP '2026-09-22 10:30:00' - TIMESTAMP '1990-03-15 08:00:00' AS substraction;text
exact_age | month_diff | substraction
-----------------------+------------+------------------------
36 years 6 mons 7 days | 6 mons 25 days | 13375 days 02:30:00两种求差方式的本质差异：
ts_a - ts_b得到的是绝对时长（天数 + 时分秒），因为TIMESTAMP - TIMESTAMP会先把结果当作精确间隔。age(a, b)得到的是日历差（年/月/日分段），更符合人类直觉。
sql
-- 同样是相差一个月多一天，两种方式结果形式不同
SELECT
age(DATE '2026-03-01', DATE '2026-01-31') AS calendar_diff,
(DATE '2026-03-01' - DATE '2026-01-31') AS absolute_days;text
calendar_diff | absolute_days
---------------+---------------
1 mon 1 day | 29比较与排序注意事项
INTERVAL 的比较是先把各字段换算成统一的"估算值"再比较：1 月按 30 天、1 天按 24 小时计算。这会导致语义上"更长"的间隔被判定为更短：
sql
-- 2026 年 2 月只有 28 天，所以"1 个月"实际比"30 天"短
SELECT
age(DATE '2026-03-01', DATE '2026-02-01') AS feb_month,
age(DATE '2026-03-01', DATE '2026-01-30') AS jan_30d_span,
(INTERVAL '1 month' > INTERVAL '30 days') AS month_gt_30d;text
feb_month | jan_30d_span | month_gt_30d
-----------+--------------+--------------
1 mon | 1 mon 2 days | f再看排序：
sql
SELECT iv FROM (VALUES
(INTERVAL '1 month'),
(INTERVAL '29 days'),
(INTERVAL '30 days'),
(INTERVAL '31 days')
) AS t(iv)
ORDER BY iv;text
iv
-----------
29 days
1 mon
30 days
31 days1 mon（换算为 30 天）排在 29 days 之后、30 days 之前。这说明 INTERVAL 上的比较与 ORDER BY 结果无法直接反映真实日历长度，跨越不同月份长度的比较要格外小心。
月末与闰年边界
与日期相加时，若目标月份没有对应日，PostgreSQL 会夹取到该月最后一天：
sql
SELECT
DATE '2026-01-31' + INTERVAL '1 month' AS jan31_plus_1m,
DATE '2026-08-31' + INTERVAL '1 month' AS aug31_plus_1m,
DATE '2026-03-31' + INTERVAL '1 month' AS mar31_plus_1m,
DATE '2026-02-28' + INTERVAL '1 year' AS feb28_plus_1y;text
jan31_plus_1m | aug31_plus_1m | mar31_plus_1m | feb28_plus_1y
---------------+---------------+---------------+---------------
2026-02-28 | 2026-09-30 | 2026-04-30 | 2027-02-28闰年规则同理：
sql
-- 2024 是闰年，2 月 29 日存在
SELECT
DATE '2024-02-29' + INTERVAL '1 year' AS leap_plus_1y,
DATE '2024-02-29' - INTERVAL '1 year' AS leap_minus_1y,
DATE '2024-02-29' + INTERVAL '1 month' AS leap_plus_1m;text
leap_plus_1y | leap_minus_1y | leap_plus_1m
--------------+---------------+--------------
2025-02-28 | 2023-02-28 | 2024-03-29IntervalStyle 输出格式
INTERVAL 的输出受 IntervalStyle 会话参数控制：
IntervalStyle | 输出示例 |
|---|---|
postgres（默认） | 1 year 2 mons 3 days 04:05:06 |
postgres_verbose | @ 1 year 2 mons 3 days 4 hours 5 mins 6 secs |
sql_standard | +1-2 +3 +4:05:06 |
iso_8601 | P1Y2M3DT4H5M6S |
sql
SHOW IntervalStyle;
SET IntervalStyle TO 'iso_8601';
SELECT INTERVAL '1 year 2 months 3 days 04:05:06' AS iso_style;text
IntervalStyle
---------------
postgres
iso_style
------------------
P1Y2M3DT4H5M6Ssql
RESET IntervalStyle;应用层对接时不要依赖默认格式，应显式 SET IntervalStyle，或者转成秒数/毫秒数输出。
实战示例
场景：会员系统需要计算用户到期时间、剩余天数，并生成续费提醒。
第 1 步：建表与数据准备
sql
DROP TABLE IF EXISTS memberships;
CREATE TABLE memberships (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
user_name TEXT NOT NULL,
plan TEXT NOT NULL CHECK (plan IN ('monthly', 'quarterly', 'yearly')),
period INTERVAL NOT NULL,
started_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
INSERT INTO memberships (user_name, plan, period, started_at) VALUES
('alice', 'monthly', INTERVAL '1 month', TIMESTAMPTZ '2026-01-31 09:00:00+08'),
('bob', 'quarterly', INTERVAL '3 months', TIMESTAMPTZ '2026-02-28 09:00:00+08'),
('carol', 'yearly', INTERVAL '1 year', TIMESTAMPTZ '2024-02-29 09:00:00+08');第 2 步：计算到期时间
sql
SET TimeZone TO 'Asia/Shanghai';
SELECT
user_name,
plan,
started_at,
started_at + period AS expires_at
FROM memberships
ORDER BY id;text
user_name | plan | started_at | expires_at
-----------+-----------+-----------------------+------------------------
alice | monthly | 2026-01-31 09:00:00+08 | 2026-02-28 09:00:00+08
bob | quarterly | 2026-02-28 09:00:00+08 | 2026-05-28 09:00:00+08
carol | yearly | 2024-02-29 09:00:00+08 | 2025-02-28 09:00:00+08
(3 rows)alice 是 1 月 31 日办的月卡，到期日被夹取到 2 月 28 日——这正是月末边界规则的自然结果。carol 是闰年 2 月 29 日办的年卡，到期日夹取到 2 月 28 日。
第 3 步：计算剩余天数与提醒
sql
SELECT
user_name,
expires_at,
expires_at - now() AS remaining_interval,
date_trunc('day', expires_at) - date_trunc('day', now()) AS remaining_days,
justify_interval(expires_at - now()) AS normalized
FROM memberships
WHERE expires_at > now()
ORDER BY expires_at;text
user_name | expires_at | remaining_interval | remaining_days | normalized
-----------+-----------------------+---------------------+----------------+-------------------
alice | 2026-02-28 09:00:00+08 | 13 days 04:30:00 | 13 | 13 days 04:30:00
bob | 2026-05-28 09:00:00+08 | 102 days 04:30:00 | 102 | 3 mons 12 days 04:30:00
carol | 2025-02-28 09:00:00+08 | -204 days 04:30:00 | -204 | -6 mons 25 days 04:30:00
(3 rows)注意 carol 的 expires_at 已经被 WHERE expires_at > now() 过滤掉了，上面只是为了展示计算效果。实际执行时结果只包含 alice 与 bob。
第 4 步：按"剩余整月"生成续费提醒
sql
SELECT
user_name,
EXTRACT(DAY FROM justify_interval(expires_at - now())) AS days_part,
EXTRACT(MONTH FROM justify_interval(expires_at - now())) AS months_part,
CASE
WHEN expires_at - now() < INTERVAL '7 days' THEN '即将到期，请尽快续费'
WHEN expires_at - now() < INTERVAL '30 days' THEN '本月内到期'
ELSE '正常'
END AS reminder
FROM memberships
WHERE expires_at > now()
ORDER BY expires_at;text
user_name | days_part | months_part | reminder
-----------+-----------+-------------+----------------------
alice | 13 | 0 | 本月内到期
bob | 12 | 3 | 正常
(2 rows)第 5 步：验证 justify_* 与比较陷阱
sql
-- 合法化只改表示，不改时长：两者与同一日期相加结果一致
SELECT
TIMESTAMP '2026-09-01' + INTERVAL '35 days' AS raw_add,
TIMESTAMP '2026-09-01' + justify_days(INTERVAL '35 days') AS justified_add;
-- 但比较时 1 mon 被当作 30 天
SELECT
INTERVAL '1 mon' > INTERVAL '31 days' AS mon_gt_31d,
INTERVAL '1 mon' = INTERVAL '30 days' AS mon_eq_30d;text
raw_add | justified_add
------------------+------------------
2026-10-06 00:00:00 | 2026-10-06 00:00:00
mon_gt_31d | mon_eq_30d
------------+------------
f | t第 6 步：清理
sql
DROP TABLE IF EXISTS memberships;
RESET TimeZone;注意事项
DATE + INTERVAL结果类型是TIMESTAMP，不是DATE。这在赋值给DATE列时会被隐式截断，容易丢时间信息。
sql
-- 不推荐：结果被截断为 date，可能产生意外舍入
CREATE TABLE t (d DATE);
INSERT INTO t VALUES (DATE '2026-01-31' + INTERVAL '1 hour');
SELECT * FROM t; -- 2026-01-31
-- 推荐：明确使用 DATE 的整数加法
SELECT DATE '2026-01-31' + 1 AS next_day;- 不要用
INTERVAL存业务时长并参与精确比较。1 月 = 30 天、1 天 = 24 小时只是比较时的估算规则，跨月与夏令时场景会失真。
sql
-- 错误！以为 1 个月 = 30 天
SELECT * FROM orders WHERE created_at > now() - INTERVAL '1 month';
-- 正确：需要"上个月的今天"时用 date_trunc 或直接写日期
SELECT * FROM orders WHERE created_at > now() - INTERVAL '1 month' AND created_at <= now();更稳妥的"最近 30 天"写法：
sql
-- 推荐：明确表达 30 天，避免月份长度歧义
SELECT * FROM orders WHERE created_at >= now() - INTERVAL '30 days';INTERVAL上用EXTRACT不会自动合法化。EXTRACT(DAY FROM INTERVAL '35 days')返回 35 而不是 5。
sql
SELECT
EXTRACT(DAY FROM INTERVAL '35 days') AS raw_day, -- 35
EXTRACT(DAY FROM justify_days(INTERVAL '35 days')) AS justified_day; -- 5overlap与range类型更适合时间区间判断。若业务是"某时间段是否冲突"，用TSRANGE配合&&比手写INTERVAL比较更清晰，见 日期时间范围。INTERVAL字段可以各自为负，参与运算时结果反直觉。写入前尽量用justify_interval规范化。IntervalStyle影响输出文本。用INTERVAL::TEXT做前后端协议不安全，跨环境可能不一致。
sql
SET IntervalStyle TO 'sql_standard';
SELECT INTERVAL '1 day 2 hours'::TEXT AS text_view;text
text_view
-----------
+1 +2:00:00sql
RESET IntervalStyle;TIME + INTERVAL会绕回 24 小时（23:30 + 2h = 01:30），不会进位到次日，容易丢失"跨天"信息。夏令时区间不是恒定的 24 小时。
TIMESTAMPTZ '2026-03-08 12:00' AT TIME ZONE 'America/New_York' + INTERVAL '1 day'的 UTC 小时数可能不是 24。
最佳实践
- 间隔字面量统一用英文单位全称（
INTERVAL '3 days 2 hours'），避免IntervalStyle与缩写歧义。 - 不要用
INTERVAL存需要精确计算的服务时长（如 SLA 秒数）。用INTEGER存秒或用两个TIMESTAMPTZ列，需要时再算差值。 INTERVAL列上建索引价值很低，业务查询通常是把间隔换算为固定单位后再过滤。
sql
-- 推荐：用生成列把间隔固化为秒，再建 B-tree 索引
CREATE TABLE tasks (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
duration INTERVAL NOT NULL,
duration_s BIGINT GENERATED ALWAYS AS (EXTRACT(EPOCH FROM duration)::BIGINT) STORED
);
CREATE INDEX idx_tasks_duration_s ON tasks (duration_s);- 展示给用户的时长用
EXTRACT拆字段，或用justify_interval后再to_char：
sql
SELECT to_char(INTERVAL '123 hours 45 minutes', 'DD"天" HH24"小时" MI"分"') AS zh_duration;text
zh_duration
--------------------
05天 03小时 45分注意 to_char 对 INTERVAL 的 DD 只做 justify_hours 后的天数换算，跨月仍不会折算月份，需要月/天混合时先用 justify_interval。
- 跨段统计按月/季/年时优先用
date_trunc+INTERVAL组合，比手工加减天数可靠：
sql
-- 生成最近 12 个月的月份序列
SELECT generate_series(
date_trunc('month', now()) - INTERVAL '11 months',
date_trunc('month', now()),
INTERVAL '1 month'
) AS month_start;- 写入前用
justify_interval规范化混合符号间隔，保证同一时长在库内只有一种表示。
下一节
继续学习：时区与 TimeZone 设置