INTERVAL 运算

INTERVAL 是 PostgreSQL 里唯一能表达「一个月」这种可变长度时间单位的类型，也是日期加减的核心工具。它的特殊之处在于内部把月（months）、日（days）、微秒（microseconds）三段分开存储、互不换算，所以 INTERVAL '1 day' 与 INTERVAL '24 hours' 在夏令时切换日会得出不同结果，而 DATE '2026-01-31' + INTERVAL '1 month' 会被截断到 2026-02-28。本节梳理 INTERVAL 的字面量写法、运算规则，以及月末截断与夏令时两个必知陷阱。

前置知识

阅读本节前，建议先了解：AGE / JUSTIFY_DAYS / JUSTIFY_HOURS

基础概念

概念	术语	说明
间隔类型	`interval`	表示一段时间长度，16 字节，由 months / days / microseconds 三段组成
字面量	Literal	`INTERVAL '1 year 2 mons'` 这类常量写法，字符串需按 `interval` 规则解析
类型修饰符	Type Modifier	`INTERVAL DAY TO SECOND`、`INTERVAL(3)` 限制可含字段与小数秒精度
月末截断	Month-end Truncation	加月时若目标月无对应日期，则取该月最后一天
夏令时	DST	`1 day` 与 `24 hours` 在切换日的真实长度不同
输出风格	IntervalStyle	`postgres` / `postgres_verbose` / `sql_standard` / `iso_8601`
归一化比较	Normalized Comparison	比较 `interval` 时按月 = 30 天、日 = 24 小时折算

三段的独立存储：

text

INTERVAL '1 year 2 mons 3 days 04:05:06.789'

  months       = 14              （year 折算成 12 个月后累加）
  days         = 3
  microseconds = 14706789000     （小时/分/秒/毫秒依次折算）

三段互不换算，这是 interval 能精确表达「自然月」的前提：
  '1 mon'     存成 months = 1
  '30 days'   存成 days   = 30
  '720 hours' 存成 microseconds = 2592000000000

只有在「比较」与「规整」时，PostgreSQL 才临时采用 30 天/月的折算比。

运算符总表（来自官方 Table 9.32，与日期时间相关的部分）：

运算符	示例	结果
`date + integer`	`DATE '2026-09-24' + 7`	`2026-10-01`（加天数，返回 `date`）
`date + interval`	`DATE '2026-09-24' + INTERVAL '1 hour'`	`2026-09-24 01:00:00`（返回 `timestamp`）
`date + time`	`DATE '2026-09-24' + TIME '10:00'`	`2026-09-24 10:00:00`
`date - date`	`DATE '2026-09-24' - DATE '2026-09-01'`	`23`（`integer` 天数）
`date - integer`	`DATE '2026-09-24' - 7`	`2026-09-17`
`date - interval`	`DATE '2026-09-24' - INTERVAL '1 hour'`	`2026-09-23 23:00:00`
`time - time`	`TIME '10:00' - TIME '09:30'`	`00:30:00`
`time - interval`	`TIME '10:00' - INTERVAL '30 min'`	`09:30:00`
`timestamp - interval`	`TIMESTAMP '2026-09-24 10:00' - INTERVAL '1 day'`	`2026-09-23 10:00:00`
`timestamp - timestamp`	`TIMESTAMP '2026-09-24' - TIMESTAMP '2026-09-01'`	`23 days`
`interval + interval`	`INTERVAL '1 day' + INTERVAL '1 hour'`	`1 day 01:00:00`
`interval - interval`	`INTERVAL '1 day' - INTERVAL '1 hour'`	`1 day -01:00:00`
`interval * number`	`INTERVAL '1 day' * 2.5`	`2 days 12:00:00`
`interval / number`	`INTERVAL '1 hour' / 3`	`00:20:00`
`interval / interval`	`INTERVAL '1 hour' / INTERVAL '30 min'`	`2`（`double precision`）
`- interval`	`- INTERVAL '1 day'`	`-1 days`

注意 interval / interval 返回的是数值（无量纲比值），而 interval / number 返回的仍是 interval，两者语义完全不同。

语法与用法

sql

-- 1. 后缀式字面量（最常用，各字段可用缩写，顺序任意）
INTERVAL '1 year 2 months 3 days 04:05:06.789'

-- 2. ISO 8601 格式
INTERVAL 'P1Y2M3DT4H5M6S'

-- 3. SQL 标准格式（需配合字段范围限定）
INTERVAL '1-2' YEAR TO MONTH          -- 1 year 2 mons
INTERVAL '3 4:05:06' DAY TO SECOND    -- 3 days 04:05:06

-- 4. 列类型声明：限制可出现的字段与小数秒精度
duration INTERVAL DAY TO SECOND
precision INTERVAL(3)

-- 5. 输出风格
SET intervalstyle = 'postgres';   -- 默认

最小可运行示例：

sql

-- 字面量解析
SELECT INTERVAL '1 year 2 months 3 days 4 hours 5 minutes 6 seconds';
-- 结果：1 year 2 mons 3 days 04:05:06

-- 缩写与复数形式都能识别
SELECT INTERVAL '1 yr 2 mon 3 d 4 h 5 min 6 s';
-- 结果：1 year 2 mons 3 days 04:05:06

-- ISO 8601 格式
SELECT INTERVAL 'P1Y2M3DT4H5M6S';
-- 结果：1 year 2 mons 3 days 04:05:06

-- 日期加间隔（返回 timestamp）
SELECT DATE '2026-09-24' + INTERVAL '1 hour';
-- 结果：2026-09-24 01:00:00

-- 日期减天数（返回 date）
SELECT DATE '2026-09-24' - 7;
-- 结果：2026-09-17

-- 间隔相乘
SELECT INTERVAL '1 day' * 2.5;
-- 结果：2 days 12:00:00

-- 间隔相除得比值
SELECT INTERVAL '1 hour' / INTERVAL '30 minutes';
-- 结果：2

四种输出风格的对照：

sql

SELECT
  INTERVAL '1 year 2 months 3 days 4 hours 5 minutes 6 seconds' AS postgres_style;

SET intervalstyle = 'postgres_verbose';
SELECT INTERVAL '1 year 2 months 3 days 4 hours 5 minutes 6 seconds';

SET intervalstyle = 'sql_standard';
SELECT INTERVAL '1 year 2 months 3 days 4 hours 5 minutes 6 seconds';

SET intervalstyle = 'iso_8601';
SELECT INTERVAL '1 year 2 months 3 days 4 hours 5 minutes 6 seconds';
RESET intervalstyle;

text

     postgres_style
--------------------------
 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

输出风格只影响显示，不影响内部存储与运算结果。跨系统对接时若对端按字符串解析，务必先约定 IntervalStyle。

详细说明

月末截断：加月的目标月不存在该日期

加 interval 时，月与日会分别处理：先把「月」部分加到年月字段上，如果得到的目标月没有该日号（例如 31 日加到 2 月），就取目标月的最后一天；随后再加「日」部分。

sql

SELECT
  DATE '2026-01-31' + INTERVAL '1 month'  AS jan31_plus_1m,
  DATE '2026-03-31' + INTERVAL '1 month'  AS mar31_plus_1m,
  DATE '2026-01-31' + INTERVAL '1 day'    AS jan31_plus_1d,
  DATE '2026-02-28' + INTERVAL '1 month'  AS feb28_plus_1m;

text

    jan31_plus_1m    |    mar31_plus_1m    | jan31_plus_1d | feb28_plus_1m
----------------------+---------------------+---------------+----------------------
 2026-02-28 00:00:00  | 2026-04-30 00:00:00 | 2026-02-01    | 2026-03-28 00:00:00

关键是截断是单向的、不可逆的：2026-01-31 + 1 mon 得到 2026-02-28，再 + 1 mon 得到 2026-03-28 而非 2026-03-31。因此「连续加 N 个月」与「一次加 N 个月」结果可能不同：

sql

SELECT
  DATE '2026-01-31' + INTERVAL '2 months'                       AS one_shot,
  DATE '2026-01-31' + INTERVAL '1 month' + INTERVAL '1 month'   AS two_steps;

text

      one_shot       |     two_steps
---------------------+---------------------
 2026-03-31 00:00:00 | 2026-03-28 00:00:00

做「按季/按年顺延」的业务（会员到期、账单周期）时，应始终用原始起始日期 + N 个周期计算，而不是在上次结果上反复累加，否则到期日会逐月漂移。

1 day 与 24 hours 在夏令时下不等价

因为「日」与「微秒」是独立存储的，INTERVAL '1 day' 表示「日历上是同一天的同一时刻」，而 INTERVAL '24 hours' 表示「绝对流逝 24 小时」。在夏令时切换日，两者会差 1 小时。官方文档给出的示例（America/Denver，2005-04-03 春季拨快一小时）：

sql

SET TimeZone = 'America/Denver';

SELECT TIMESTAMPTZ '2005-04-02 12:00-07' + INTERVAL '1 day'    AS plus_1_day,
       TIMESTAMPTZ '2005-04-02 12:00-07' + INTERVAL '24 hours' AS plus_24h;

text

      plus_1_day       |      plus_24h
-----------------------+-----------------------
 2005-04-03 12:00:00-06 | 2005-04-03 13:00:00-06

加 1 day 保持「墙上时间 12:00」不变，偏移从 -07 变为 -06；加 24 hours 则严格推进 86400 秒，墙上时间变成 13:00。业务上表达「明天同一时刻」要用 1 day，表达「24 小时后」要用 24 hours。

用 UTC 会话时区就不存在这个差异（UTC 无夏令时）：

sql

SET TimeZone = 'UTC';
SELECT TIMESTAMPTZ '2005-04-02 12:00-07' + INTERVAL '1 day'    AS plus_1_day,
       TIMESTAMPTZ '2005-04-02 12:00-07' + INTERVAL '24 hours' AS plus_24h;

text

      plus_1_day       |      plus_24h
-----------------------+-----------------------
 2005-04-02 19:00:00+00 | 2005-04-02 19:00:00+00

这也解释了为什么跨时区业务建议在 UTC 下做时间运算，只在展示时转换成本地时区。

用 make_* 系列动态构造日期与间隔

字面量只能写常量，需要按变量拼装时要用 make_interval 与 make_* 系列函数：

sql

-- make_interval(years, months, weeks, days, hours, mins, secs)
-- 全部参数都有默认值，可按名传参
SELECT
  make_interval(days => 7)                                  AS one_week,
  make_interval(months => 3, days => 15)                    AS three_months_15d,
  make_interval(hours => 2, mins => 30)                     AS two_and_half_h,
  make_interval(secs => 90)                                 AS ninety_secs;

text

  one_week  | three_months_15d  | two_and_half_h | ninety_secs
------------+-------------------+----------------+-------------
 7 days     | 3 mons 15 days    | 02:30:00       | 00:01:30

sql

-- make_date / make_time / make_timestamp / make_timestamptz
SELECT
  make_date(2026, 9, 24)                             AS d,
  make_time(14, 30, 45.5)                            AS t,
  make_timestamp(2026, 9, 24, 14, 30, 45.5)          AS ts,
  make_timestamptz(2026, 9, 24, 14, 30, 45.5, 'Asia/Shanghai') AS tstz;

text

     d      |     t       |            ts            |              tstz
------------+-------------+--------------------------+--------------------------------
 2026-09-24 | 14:30:45.5  | 2026-09-24 14:30:45.5    | 2026-09-24 14:30:45.5+08

make_timestamptz 的第六个参数省略时使用会话时区；显式传入时区名可让结果与会话配置解耦，推荐显式传入。

make_interval 的各参数类型为 integer（仅 secs 为 double precision），因此无法用它构造「1.5 天」这类小数单位。需要小数倍时长时改用乘法，乘法作用在天与时间两个字段上最直观：

sql

SELECT INTERVAL '3 days' * 1.5;    -- 4 days 12:00:00
SELECT INTERVAL '90 minutes' * 1.5; -- 02:15:00

注意乘法是逐字段进行的：month 字段会先把整数部分取整，再把小数部分按 30 天/月的固定比例折算到 day 字段，因此对含 month 的 interval 乘小数结果可能不直观，建议需要小数倍时长时改用「天 + 小时」表达。

实战示例

sql

-- 示例库：demo
DROP TABLE IF EXISTS plan_catalog;
CREATE TABLE plan_catalog (
  id            BIGSERIAL PRIMARY KEY,
  plan_code     TEXT          NOT NULL UNIQUE,
  price         NUMERIC(10,2) NOT NULL,
  duration      INTERVAL      NOT NULL,        -- 套餐时长
  grace_period  INTERVAL      NOT NULL DEFAULT INTERVAL '7 days'
);

DROP TABLE IF EXISTS subscriptions;
CREATE TABLE subscriptions (
  id          BIGSERIAL PRIMARY KEY,
  user_id     BIGINT        NOT NULL,
  plan_code   TEXT          NOT NULL,
  started_at  TIMESTAMPTZ   NOT NULL,
  expires_at  TIMESTAMPTZ   NOT NULL,
  canceled_at TIMESTAMPTZ
);

INSERT INTO plan_catalog (plan_code, price, duration, grace_period) VALUES
  ('monthly',   29.00, INTERVAL '1 month',  INTERVAL '3 days'),
  ('quarterly', 79.00, INTERVAL '3 months', INTERVAL '7 days'),
  ('yearly',   299.00, INTERVAL '1 year',   INTERVAL '15 days'),
  ('trial',      0.00, INTERVAL '14 days',  INTERVAL '1 day');

-- 造数：每个套餐各 60 条订阅，起始日特意选在月末以覆盖截断
INSERT INTO subscriptions (user_id, plan_code, started_at, expires_at)
SELECT
  1000 + i,
  (ARRAY['monthly','quarterly','yearly','trial'])[1 + (i % 4)],
  TIMESTAMPTZ '2026-01-31 08:00:00+08' + ((i % 30) * INTERVAL '1 day'),
  TIMESTAMPTZ '2026-01-31 08:00:00+08' + ((i % 30) * INTERVAL '1 day')
    + (ARRAY[INTERVAL '1 month', INTERVAL '3 months', INTERVAL '1 year', INTERVAL '14 days'])[1 + (i % 4)]
FROM generate_series(1, 240) AS i;

CREATE INDEX idx_subscriptions_expires_at ON subscriptions (expires_at);
ANALYZE subscriptions;

步骤 1：按套餐计算到期时间（起始时刻 + 时长）

sql

SELECT
  s.id,
  s.plan_code,
  s.started_at,
  p.duration,
  s.started_at + p.duration AS calc_expires_at
FROM subscriptions s
JOIN plan_catalog p USING (plan_code)
ORDER BY s.id
LIMIT 5;

预期输出：

text

 id | plan_code |      started_at       |  duration  |    calc_expires_at
----+-----------+-----------------------+------------+-----------------------
  1 | quarterly | 2026-01-31 08:00:00+08 | 3 mons     | 2026-04-30 08:00:00+08
  2 | yearly    | 2026-02-01 08:00:00+08 | 1 year     | 2027-02-01 08:00:00+08
  3 | trial     | 2026-02-02 08:00:00+08 | 14 days    | 2026-02-16 08:00:00+08
  4 | monthly   | 2026-02-03 08:00:00+08 | 1 mon      | 2026-03-03 08:00:00+08
  5 | quarterly | 2026-02-04 08:00:00+08 | 3 mons     | 2026-05-04 08:00:00+08

第 1 行体现了月末截断：2026-01-31 + 3 months 目标月是 4 月（只有 30 天），因此落到 2026-04-30。

步骤 2：找出「加一个月后日期被截断」的记录

sql

-- 对比「起始日 + 1 月」与「起始日所在月的次月同日」是否一致
SELECT
  started_at::date                              AS start_date,
  (started_at + INTERVAL '1 month')::date       AS plus_1m,
  EXTRACT(DAY FROM started_at)                  AS start_day,
  EXTRACT(DAY FROM started_at + INTERVAL '1 month') AS end_day
FROM subscriptions
WHERE EXTRACT(DAY FROM started_at) <> EXTRACT(DAY FROM started_at + INTERVAL '1 month')
ORDER BY started_at
LIMIT 5;

text

 start_date |  plus_1m   | start_day | end_day
------------+------------+-----------+---------
 2026-01-31 | 2026-02-28 |        31 |      28
 2026-03-31 | 2026-04-30 |        31 |      30
 2026-05-31 | 2026-06-30 |        31 |      30
 2026-07-31 | 2026-08-31 |        31 |      31（未被截断，仅因起始日不同被筛出）

上线前应专门跑这类核对 SQL，确认截断行为符合业务预期。若业务要求「31 日起的订阅永远在次月最后一天到期」，则当前行为正确；若要求「固定 30 天」，就必须改用 INTERVAL '30 days'。

步骤 3：宽限期与剩余时长计算

sql

-- 到期后仍有 N 天宽限期，计算宽限期截止时刻与剩余小时数
SELECT
  s.id,
  s.expires_at,
  p.grace_period,
  s.expires_at + p.grace_period AS grace_deadline,
  s.expires_at - now()          AS remaining
FROM subscriptions s
JOIN plan_catalog p USING (plan_code)
WHERE s.expires_at > now()
ORDER BY s.expires_at
LIMIT 5;

text

 id |      expires_at       | grace_period |   grace_deadline      |     remaining
----+-----------------------+--------------+-----------------------+-------------------
  7 | 2026-10-01 08:00:00+08| 1 mon        | 2026-11-01 08:00:00+08| 7 days 02:15:33

步骤 4：把 interval 转成可比较的秒数

sql

SELECT
  plan_code,
  duration,
  EXTRACT(EPOCH FROM duration)::NUMERIC(14,0)        AS duration_seconds,
  EXTRACT(EPOCH FROM duration) / 86400               AS duration_days_approx
FROM plan_catalog
ORDER BY duration;

text

 plan_code |  duration  | duration_seconds | duration_days_approx
-----------+------------+------------------+----------------------
 trial     | 14 days    |          1209600 |               14.000
 monthly   | 1 mon      |          2592000 |               30.000
 quarterly | 3 mons     |          7776000 |               90.000
 yearly    | 1 year     |         31536000 |              365.000

EXTRACT(EPOCH FROM interval) 一律按 1 月 = 30 天 折算，因此 1 mon 恒等于 2592000 秒。这个数字用于粗算可以，用于精确计费不行——真实月份有 28~31 天。

步骤 5：用 make_interval 动态续费

sql

-- 按「续费月数」动态延长有效期，避免字符串拼接
WITH renew AS (
  SELECT
    id,
    expires_at,
    make_interval(months => 6) AS extend_by
  FROM subscriptions
  WHERE plan_code = 'quarterly'
    AND expires_at < TIMESTAMPTZ '2026-12-31 00:00:00+08'
)
UPDATE subscriptions s
SET expires_at = r.expires_at + r.extend_by
FROM renew r
WHERE s.id = r.id
RETURNING s.id, s.expires_at;

text

 id |      expires_at
----+-----------------------
  1 | 2026-10-30 08:00:00+08
  5 | 2026-11-04 08:00:00+08

注意事项

date + interval 返回 timestamp（无时区），不是 date。想留在 date 域请改用整数天数：

sql

SELECT pg_typeof(DATE '2026-09-24' + INTERVAL '1 day');   -- timestamp without time zone
SELECT pg_typeof(DATE '2026-09-24' + 1);                  -- date

date - date 得到整数天数，date - interval 得到 timestamp，两者类型不同，不要混用在同一个 UNION 分支里。

加月会被月末截断，且不可逆：

sql

-- 不推荐：在结果上反复累加，到期日会漂移
SET expires_at = expires_at + INTERVAL '1 month';
-- 推荐：从原始起始日重算
SET expires_at = started_at + INTERVAL '1 month' * n;

INTERVAL '1 day' 与 INTERVAL '24 hours' 不等价，夏令时切换日相差 1 小时。表达「明天同一墙上时刻」用 1 day。
interval / interval 返回 double precision，不是 interval，别把它当成 interval 继续参与日期运算：
sql
```
SELECT pg_typeof(INTERVAL '2 hours' / INTERVAL '1 hour');   -- double precision
```
interval * interval 不存在，会报 operator does not exist: interval * interval。要放大时长请乘以数值。

EXTRACT(EPOCH FROM interval) 按 30 天/月折算，拿去算真实计费天数会偏：

sql

SELECT EXTRACT(EPOCH FROM INTERVAL '1 month') / 86400;   -- 30，但 2 月只有 28 天

interval 比较会做归一化，结果可能反直觉：
sql
```
SELECT INTERVAL '30 days' = INTERVAL '1 mon' AS eq,
       INTERVAL '24 hours' = INTERVAL '1 day' AS eq2;
```
两者在比较时会被折算到同一刻度，因此都为 true；但它们在加法中产生的时间戳并不总是相同。
声明列类型时应限制字段范围。默认 INTERVAL 接受任意字段组合，容易存入「1 月 40 天」这类未规整值：
sql
```
-- 推荐：明确只能是「天到秒」
duration INTERVAL DAY TO SECOND
```
INTERVAL(3) 只限制小数秒精度，不限制字段范围，两者需配合使用：
sql
```
precision INTERVAL(3) DAY TO SECOND
```

字符串拼接构造 interval 有注入与解析失败风险，动态值一律用 make_interval：

sql

-- 不推荐
EXECUTE format('SELECT now() + interval ''%s days''', user_input);
-- 推荐
SELECT now() + make_interval(days => user_input);

在列上做 col + INTERVAL '1 day' < now() 这类条件无法使用 col 上的普通索引，应把间隔移到另一侧：

sql

-- 不推荐：col 被表达式包裹
WHERE expires_at + INTERVAL '7 days' < now()
-- 推荐：可走 idx_subscriptions_expires_at
WHERE expires_at < now() - INTERVAL '7 days'

最佳实践

列类型明确到字段范围：时长类字段用 INTERVAL DAY TO SECOND，周期类字段用 INTERVAL YEAR TO MONTH，让非法值在写入时就被拒绝。
区分「日历时长」与「绝对时长」：周期/订阅/账单用 N months，超时/心跳/缓存 TTL 用 N minutes 或秒数，不要混用。
续费与顺延一律基于原始起始日重算，绝不在上次结果上累加，避免月末截断误差累积：
sql
```
SELECT started_at + (INTERVAL '1 month' * periods) AS expires_at;
```
对 timestamptz 的时间运算尽量在 UTC 会话下完成，只在输出时转换时区，可以彻底规避夏令时带来的歧义。
动态间隔统一用 make_interval，不要拼字符串；它同时解决了类型安全与 SQL 注入两个问题。
动态日期统一用 make_date / make_timestamp 系列，特别是 make_timestamptz 要显式传时区名。
需要「几秒后」这类固定时长时，直接算时间戳而不是存 interval：
sql
```
-- 推荐：索引友好
WHERE expires_at < now() - make_interval(mins => 30)
```
核对月末截断行为时写专门的核对 SQL（如步骤 2），把「起始日号数不等于结束日号数」的记录捞出来。
interval 字段的默认值用字面量而非 make_interval，字面量在 DEFAULT 中同样是常量折叠，更直观：
sql
```
grace_period INTERVAL NOT NULL DEFAULT INTERVAL '7 days'
```
跨系统传输 interval 时显式约定格式，推荐统一设为 ISO 8601：
sql
```
SET intervalstyle = 'iso_8601';
```
不要把 interval 存成秒数再自己换算成月，那会丢掉「自然月」语义；需要精确月数的场景直接存 interval 或存「起止日期对」。
较新版本的 PostgreSQL 还提供 date_add() / date_subtract()，它们支持显式传入时区参数，可作为 ts + interval 的补充写法；使用时请对照你所部署版本的官方文档确认可用性。

继续学习：TO_CHAR / TO_DATE / TO_TIMESTAMP

INTERVAL 运算 ​

基础概念 ​

语法与用法 ​

详细说明 ​

月末截断：加月的目标月不存在该日期 ​

1 day 与 24 hours 在夏令时下不等价 ​

用 make_* 系列动态构造日期与间隔 ​

实战示例 ​

注意事项 ​

最佳实践 ​

参考链接 ​

INTERVAL 运算

基础概念

语法与用法

详细说明

月末截断：加月的目标月不存在该日期

1 day 与 24 hours 在夏令时下不等价

用 make_* 系列动态构造日期与间隔

实战示例

注意事项

最佳实践

参考链接