DATE_TRUNC 截断

date_trunc() 把时间戳「向下取整」到指定的精度边界——截到月就是当月 1 日 0 点，截到周就是本周周一 0 点。它是做时间分组报表最常用的函数：GROUP BY date_trunc('month', created_at) 一行就能把上千条订单按月份汇总。但 date_trunc 的 week 截断到周一（ISO 规则）而非周日，quarter 会得到季初日期，对 timestamptz 截断时用的是会话时区——这三点是实际项目中最常踩的坑。

前置知识

阅读本节前，建议先了解：EXTRACT / DATE_PART

基础概念

date_trunc 本质是一个「取整」函数：它保留精度字段以上的部分，把精度字段以下的部分全部置零，返回类型与输入类型相同。

概念	术语	说明
截断	Truncate	向下取整到精度边界（不是四舍五入）
精度字段	Field	`microseconds` / `milliseconds` / `second` / `minute` / `hour` / `day` / `week` / `month` / `quarter` / `year` 等
时区锚点	Time Zone Anchor	对 `timestamptz` 截断时决定「一天从几点开始」
ISO 周	ISO Week	周一为一周开始，`week` 字段截断到周一
三参数版	Three-argument form	`date_trunc(field, timestamptz, zone)`，显式指定截断时区
时间分箱	Time Binning	`date_bin()`（PostgreSQL 14+）按固定间隔分箱，不依赖日历
边界对齐	Boundary Alignment	截断结果总是边界起点，而非区间中点

field 清单（来自官方文档，interval 与 timestamp 共用）：

field	截断效果（以 `2026-09-24 15:47:33.123456` 为例）
`microseconds`	`2026-09-24 15:47:33.123456`（无变化）
`milliseconds`	`2026-09-24 15:47:33.123`
`second`	`2026-09-24 15:47:33`
`minute`	`2026-09-24 15:47:00`
`hour`	`2026-09-24 15:00:00`
`day`	`2026-09-24 00:00:00`
`week`	`2026-09-21 00:00:00`（本周一）
`month`	`2026-09-01 00:00:00`
`quarter`	`2026-07-01 00:00:00`（第三季度起点）
`year`	`2026-01-01 00:00:00`
`decade`	`2020-01-01 00:00:00`
`century`	`2001-01-01 00:00:00`
`millennium`	`2001-01-01 00:00:00`

注意 century 与 millennium 的起点是 2001 而不是 2000：公历没有「0 世纪」，第 21 世纪从 2001-01-01 开始。这与 EXTRACT(CENTURY ...) 的编号规则一致。

截断 vs 抽取的区别：

text

EXTRACT(MONTH FROM ts)  →  9            （返回一个数字，用于比较/运算）
date_trunc('month', ts) →  2026-09-01   （返回一个时间戳，用于分组/展示）

报表分组要用 date_trunc，因为分组键需要保持「时间戳」形态，
才能同时用于 SELECT 展示、ORDER BY 排序、以及范围条件过滤。

语法与用法

sql

-- 1. 基础形式：field 为字符串字面量，src 为 timestamp / timestamptz / interval
date_trunc(field text, source timestamp) → timestamp
date_trunc(field text, source timestamptz) → timestamptz
date_trunc(field text, source interval) → interval

-- 2. 三参数形式（PostgreSQL 18 文档 Table 9.33 明确列出）
date_trunc(field text, source timestamptz, time_zone text) → timestamptz

最小可运行示例：

sql

-- 截断到天：去掉时分秒
SELECT date_trunc('day', TIMESTAMP '2026-09-24 15:47:33.123456');
-- 结果：2026-09-24 00:00:00

-- 截断到月：用于按月分组
SELECT date_trunc('month', TIMESTAMP '2026-09-24 15:47:33');
-- 结果：2026-09-01 00:00:00

-- 截断到周：注意是周一
SELECT date_trunc('week', TIMESTAMP '2026-09-24 15:47:33');
-- 结果：2026-09-21 00:00:00   （2026-09-21 是周一）

-- 对 timestamptz 截断：结果依赖会话时区
SHOW TimeZone;   -- 假设为 Asia/Shanghai
SELECT date_trunc('day', TIMESTAMPTZ '2026-09-24 23:30:00+08');
-- 结果：2026-09-24 00:00:00+08

-- 三参数版：强制按指定时区截断
SELECT date_trunc('day', TIMESTAMPTZ '2026-09-24 23:30:00+08', 'UTC');
-- 结果：2026-09-24 08:00:00+08   （即 UTC 的 2026-09-24 00:00:00）

详细说明

对 timestamptz 截断依赖会话时区

date_trunc('day', timestamptz) 在内部的执行顺序是：先把 timestamptz 按会话 TimeZone 转换成当地时间 → 在本地时间上截断 → 再转回 timestamptz。因此同一条 SQL 在不同会话时区下会得到不同结果：

sql

SET TimeZone = 'Asia/Shanghai';
SELECT date_trunc('day', TIMESTAMPTZ '2026-09-24 23:30:00+00');
-- 结果：2026-09-24 00:00:00+08
-- （UTC 的 23:30 在上海已是 25 日 07:30，截断到 25 日 00:00+08）

这里的关键是：TIMESTAMPTZ '2026-09-24 23:30:00+00' 表示的是同一个绝对时刻，无论会话时区怎么设，这个时刻都不变；变的只是「把它归到哪一天」。用 UTC 会话时区再看：

sql

SET TimeZone = 'UTC';
SELECT date_trunc('day', TIMESTAMPTZ '2026-09-24 23:30:00+00');
-- 结果：2026-09-24 00:00:00+00

结论：跨时区业务必须在截断前显式指定时区，否则报表数字会随服务器/会话配置漂移。三参数版就是为此设计的：

sql

-- 无论会话时区如何设置，都按 Asia/Shanghai 的自然日截断
SELECT date_trunc('day', paid_at, 'Asia/Shanghai') FROM orders;

三参数版的返回类型仍是 timestamptz，但边界是按给定时区的本地自然日计算的。

week / quarter / year 的边界规则

week 采用 ISO 8601 规则：一周从周一开始，到周日结束。官方 EXTRACT(WEEK ...) 与 date_trunc('week', ...) 使用同一套周编号体系，因此它们可以安全配对：

sql

SELECT
  ts,
  date_trunc('week', ts)  AS week_start,   -- 周一
  EXTRACT(WEEK FROM ts)   AS iso_week,     -- ISO 周号
  EXTRACT(ISOYEAR FROM ts) AS iso_year     -- ISO 年（跨年周归属）
FROM (VALUES
  (TIMESTAMP '2026-09-21 10:00:00'),   -- 周一
  (TIMESTAMP '2026-09-22 10:00:00'),   -- 周二
  (TIMESTAMP '2026-09-27 10:00:00'),   -- 周日
  (TIMESTAMP '2026-09-28 10:00:00')    -- 下周一
) AS t(ts);

text

         ts          |     week_start      | iso_week | iso_year
---------------------+---------------------+----------+----------
 2026-09-21 10:00:00 | 2026-09-21 00:00:00 |       39 |     2026
 2026-09-22 10:00:00 | 2026-09-21 00:00:00 |       39 |     2026
 2026-09-27 10:00:00 | 2026-09-21 00:00:00 |       39 |     2026
 2026-09-28 10:00:00 | 2026-09-28 00:00:00 |       40 |     2026

周日的 week_start 落在上周一（2026-09-21），而不是它自己所在的那一天——这正是 ISO 周的定义。

quarter 把一年切成四段，起点分别是 1/1、4/1、7/1、10/1：

sql

SELECT
  date_trunc('quarter', TIMESTAMP '2026-09-24') AS q3_start,
  date_trunc('quarter', TIMESTAMP '2026-10-01') AS q4_start;
-- 结果：2026-07-01 00:00:00  |  2026-10-01 00:00:00

year 截断到 1 月 1 日 0 点。若要按「财年」分组（例如 4 月起的财年），需要手动偏移：

sql

-- 以 4 月 1 日为财年起点，返回财年起始日期
SELECT date_trunc('year', ts - INTERVAL '3 months') + INTERVAL '3 months' AS fiscal_year_start
FROM orders;

date_bin 与固定间隔分箱

date_trunc 只能截断到日历单位，无法做「每 15 分钟」「每 7 天」这类任意间隔分箱。PostgreSQL 14 引入 date_bin() 解决这个问题：

sql

-- 每 15 分钟一个箱
SELECT date_bin(INTERVAL '15 minutes', TIMESTAMP '2026-09-24 15:47:33', TIMESTAMP '2026-09-24 00:00:00');
-- 结果：2026-09-24 15:45:00

-- 每 8 小时一个箱（第三参数是分箱原点）
SELECT date_bin(INTERVAL '8 hours', TIMESTAMP '2026-09-24 15:47:33', TIMESTAMP '2026-09-24 00:00:00');
-- 结果：2026-09-24 08:00:00

date_bin(stride, source, origin) 三个参数：stride 是箱宽，source 是待分箱的时间，origin 是分箱网格的原点。所有箱的边界都是 origin + n * stride。

date_trunc 与 date_bin 的分工：

场景	用哪个	原因
按天/月/季/年汇总	`date_trunc`	日历单位长度不等（月有 28~31 天）
按周汇总	`date_trunc('week', ...)`	天然对齐 ISO 周一
每 15 分钟/30 秒/2 小时	`date_bin`	固定间隔，与日历无关
滚动窗口切片	`date_bin`	原点可自定义

实战示例

sql

-- 示例库：demo
DROP TABLE IF EXISTS orders;
CREATE TABLE orders (
  id          BIGSERIAL PRIMARY KEY,
  user_id     BIGINT        NOT NULL,
  amount      NUMERIC(12,2) NOT NULL,
  status      TEXT          NOT NULL,          -- paid / refunded / pending
  created_at  TIMESTAMPTZ   NOT NULL,
  paid_at     TIMESTAMPTZ
);

-- 造数：2026 年前 9 个月，每月 60 条
INSERT INTO orders (user_id, amount, status, created_at, paid_at)
SELECT
  1000 + (i % 200),
  round((random() * 900 + 100)::numeric, 2),
  (ARRAY['paid','paid','paid','refunded','pending'])[1 + (i % 5)],
  TIMESTAMPTZ '2026-01-01 00:00:00+08' + ((i % 270) * INTERVAL '1 day') + ((i % 24) * INTERVAL '1 hour'),
  CASE WHEN (i % 5) = 4 THEN NULL
       ELSE TIMESTAMPTZ '2026-01-01 00:00:00+08' + ((i % 270) * INTERVAL '1 day') + ((i % 24) * INTERVAL '2 hour')
  END
FROM generate_series(1, 540) AS i;

CREATE INDEX idx_orders_created_at ON orders (created_at);
ANALYZE orders;

步骤 1：按月汇总已支付金额

sql

SELECT
  date_trunc('month', paid_at) AS month,
  count(*)                     AS paid_cnt,
  sum(amount)                  AS paid_amount
FROM orders
WHERE paid_at IS NOT NULL
  AND status = 'paid'
GROUP BY 1
ORDER BY 1;

预期输出：

text

        month        | paid_cnt | paid_amount
---------------------+----------+-------------
 2026-01-01 00:00:00 |       36 |     17935.40
 2026-02-01 00:00:00 |       32 |     16030.85
 2026-03-01 00:00:00 |       36 |     18120.00
 ...                 |      ... |         ...

步骤 2：按周汇总订单量（跨年周也能正确归属）

sql

SELECT
  date_trunc('week', created_at) AS week_start,
  count(*)                       AS order_cnt
FROM orders
GROUP BY 1
ORDER BY 1
LIMIT 5;

text

     week_start        | order_cnt
-----------------------+-----------
 2026-01-01 00:00:00   |        20   -- 2026-01-01 是周四，本周从周一 2025-12-29 起算

注意：date_trunc('week', ...) 返回的是该周周一的 0 点。2026-01-01 是周四，因此它所属的 ISO 周从 2025-12-29（周一）起算，截断结果落在 2025-12-29，跨了年。这也是「按周分组」必须区分「周起始日」与「自然年」的原因。

步骤 3：按小时统计下单时段分布

sql

SELECT
  EXTRACT(HOUR FROM created_at AT TIME ZONE 'Asia/Shanghai') AS hour_of_day,
  count(*)                                                    AS cnt
FROM orders
GROUP BY 1
ORDER BY 1
LIMIT 5;

text

 hour_of_day | cnt
-------------+-----
           0 |  23
           1 |  22
           2 |  22
           3 |  23
           4 |  23

步骤 4：用 date_bin 做 15 分钟颗粒度分析

sql

-- 统计当天每 15 分钟的下单量（取某一天作为示例）
SELECT
  date_bin(INTERVAL '15 minutes', created_at, TIMESTAMPTZ '2026-01-01 00:00:00+08') AS slot,
  count(*) AS cnt
FROM orders
WHERE created_at >= TIMESTAMPTZ '2026-01-01 00:00:00+08'
  AND created_at <  TIMESTAMPTZ '2026-01-02 00:00:00+08'
GROUP BY 1
ORDER BY 1
LIMIT 5;

text

              slot               | cnt
---------------------------------+-----
 2026-01-01 00:00:00+08          |   2
 2026-01-01 02:00:00+08          |   2
 2026-01-01 04:00:00+08          |   2
 2026-01-01 06:00:00+08          |   2
 2026-01-01 08:00:00+08          |   2

步骤 5：验证表达式索引能加速按月分组

sql

CREATE INDEX idx_orders_paid_month ON orders (date_trunc('month', paid_at));

EXPLAIN (ANALYZE, COSTS OFF)
SELECT date_trunc('month', paid_at), count(*)
FROM orders
WHERE date_trunc('month', paid_at) >= TIMESTAMPTZ '2026-06-01 00:00:00+08'
GROUP BY 1
ORDER BY 1;

text

                                              QUERY PLAN
------------------------------------------------------------------------------------------------------
 GroupAggregate
   Group Key: date_trunc('month'::text, paid_at)
   ->  Index Scan using idx_orders_paid_month on orders
         Index Cond: (date_trunc('month'::text, paid_at) >= '2026-06-01 00:00:00+08'::timestamp with time zone)
 (3 rows)

索引定义里的表达式必须与查询里逐字符一致（函数名、引号、空格），否则优化器无法匹配。

注意事项

date_trunc 是向下取整，不是四舍五入。date_trunc('hour', '15:47:33') 得到 15:00:00 而非 16:00:00。

week 截断到周一，不是周日。若业务上以周日为一周开始，必须手动减一天：

sql

SELECT date_trunc('week', ts + INTERVAL '1 day') - INTERVAL '1 day' AS sunday_week_start FROM orders;

对 timestamptz 截断严格依赖会话时区。同一条 SQL 在 Asia/Shanghai 与 UTC 会话下，跨零点数据会被分到不同天：

sql

-- 有隐患
SELECT date_trunc('day', paid_at) FROM orders;
-- 推荐：显式指定业务时区
SELECT date_trunc('day', paid_at, 'Asia/Shanghai') FROM orders;

不要对 date 类型使用 date_trunc('hour', ...)，虽然能执行，但会得到无意义的 00:00:00 而掩盖类型错误：
sql
```
-- 不推荐：date 上没有小时概念
SELECT date_trunc('hour', DATE '2026-09-24');
```
date_trunc 对 interval 的语义与对 timestamp 不同。对 interval 截断是把该段的小单位清零，例如 date_trunc('hour', INTERVAL '3 days 15:47:33') 得到 3 days 15:00:00，不会把「天」抹掉。
精度不足的字段会静默返回原值。date_trunc('microseconds', ts) 实际上什么也不做，写错字段名时容易误以为生效。
century / millennium 的边界是 2001 年而非 2000 年，若按「世纪 = 整百年」的直觉使用会偏差一年。
表达式索引有写放大成本。每张表的 date_trunc 表达式索引都会在每次 INSERT/UPDATE 时重算并写入，高频写入表要权衡：
sql
```
-- 只在确实需要按截断值过滤时才建
CREATE INDEX idx_orders_paid_month ON orders (date_trunc('month', paid_at));
```
GROUP BY date_trunc(...) - INTERVAL '...' 这类「先运算再分组」会破坏索引匹配。想用索引，必须在 GROUP BY 里原样写表达式。
date_bin 要求 stride 为正的 interval，传 INTERVAL '-15 minutes' 会报错 stride must be greater than zero。
date_bin 的 source 与 origin 类型必须一致，混用 timestamp 与 timestamptz 会报 function date_bin(interval, timestamp without time zone, timestamp with time zone) does not exist。
date_trunc 结果用于 WHERE 时，索引只能用于「范围条件」而非「等值」分组后的过滤；直接 WHERE date_trunc('month', paid_at) = '2026-09-01' 会走表达式索引但代价较高，更好的写法是范围条件：
sql
```
-- 推荐：可走 idx_orders_paid_at
WHERE paid_at >= TIMESTAMPTZ '2026-09-01 00:00:00+08'
  AND paid_at <  TIMESTAMPTZ '2026-10-01 00:00:00+08'
```

最佳实践

报表分组的键一律用 date_trunc 生成，不要用 EXTRACT 拼字符串，前者天然可排序、可作范围条件。
对 timestamptz 分组时，一律使用三参数版显式指定业务时区，让结果不受服务器与客户端配置影响。
把业务时区抽成常量或配置项，不要在几十条 SQL 里硬编码 'Asia/Shanghai'：
sql
```
SELECT date_trunc('month', paid_at, current_setting('app.biz_timezone', true));
```

按周汇总时同时输出 week_start 与 ISO 周号，方便业务方核对：

sql

SELECT date_trunc('week', paid_at) AS week_start,
       EXTRACT(WEEK FROM paid_at) AS iso_week,
       count(*)
FROM orders GROUP BY 1, 2 ORDER BY 1;

固定间隔统计优先用 date_bin，不要用 date_trunc('hour', ...) 再手工拼凑 15 分钟档位。
索引表达式与查询表达式保持逐字符一致，建议把截断表达式定义为视图或生成列，避免各处手写漂移。
大表按时间分组的报表优先在 WHERE 里用范围条件裁剪分区/索引，让 date_trunc 只处理裁剪后的小结果集。
分组结果用 ORDER BY 1 显式排序，不要依赖数据库的偶然顺序，报表消费方需要稳定输出。
对 paid_at IS NULL 的行先过滤再分组，否则会出现一个 NULL 分组，GROUP BY 会把所有未支付订单归入同一组：
sql
```
WHERE paid_at IS NOT NULL
```
date_trunc('day', ts) 与 ts::date 在 timestamp 上结果等价，但在 timestamptz 上不等价：ts::date 受会话时区影响且丢失时间精度，做范围条件时用 date_trunc 或显式范围更安全。
月末/季末边界要写测试覆盖：date_trunc('month', '2026-01-31')、date_trunc('quarter', '2026-12-31')、闰年 2028-02-29。
报表展示层再做格式化，SQL 层只返回时间戳，用 to_char 或前端格式化，避免在聚合层引入字符串导致无法排序。

继续学习：AGE / JUSTIFY_DAYS / JUSTIFY_HOURS

DATE_TRUNC 截断 ​

基础概念 ​

语法与用法 ​

详细说明 ​

对 timestamptz 截断依赖会话时区 ​

week / quarter / year 的边界规则 ​

date_bin 与固定间隔分箱 ​

实战示例 ​

注意事项 ​

最佳实践 ​

参考链接 ​

DATE_TRUNC 截断

基础概念

语法与用法

详细说明

对 timestamptz 截断依赖会话时区

week / quarter / year 的边界规则

date_bin 与固定间隔分箱

实战示例

注意事项

最佳实践

参考链接