PHP小码农

Appearance

日期时间格式化 ​

本节讲解 PostgreSQL 日期时间值的输入解析与输出格式化。你将掌握 to_char 模式串、to_date / to_timestamp 解析、EXTRACT 与 date_trunc 截断、ISO 8601 与 ISO 周体系，以及报表统计中的常见写法。

前置知识

阅读本节前，建议先了解：时区与 TimeZone 设置

基础概念 ​

PostgreSQL 把「日期时间的内部值」和「日期时间的文本表示」严格分开。前者是存储层的二进制值，后者由格式化函数或会话参数决定。理解这个分离是避免格式化陷阱的前提。

两类函数：输出格式化与输入解析 ​

函数方向作用典型用途
to_char(timestamp, text)值 → 文本按模式串输出生成报表、导出文件
to_char(interval, text)值 → 文本间隔格式化展示"用时 1小时30分"
to_date(text, text)文本 → 值解析为 DATE导入外部数据
to_timestamp(text, text)文本 → 值解析为 TIMESTAMPTZ导入带时区的时间
to_number(text, text)文本 → 值数值解析（相关）清洗财务数据

注意 to_timestamp 有两个重载：to_timestamp(double precision) 把 Unix 时间戳转成 TIMESTAMPTZ，to_timestamp(text, text) 按模式串解析文本。参数类型不同，行为完全不同。

两类截断函数：EXTRACT 与 date_trunc ​

函数返回类型作用示例结果
EXTRACT(field FROM ts)numeric取出单个字段EXTRACT(YEAR FROM ...) → 2026
date_part(text, ts)double precision同上（老写法）date_part('year', ...) → 2026
date_trunc(field, ts)同输入类型向下截断到指定精度date_trunc('month', ...) → 月初零点

EXTRACT 返回 numeric，date_part 返回 double precision。两者功能重叠，新代码推荐 EXTRACT，因为它能返回精确的 numeric（对 INTERVAL 的秒值尤其重要）。

关键术语：

  • 模式串（Pattern）：由模式字符和普通文本组成的模板，如 'YYYY-MM-DD'。
  • ISO 8601：国际标准化组织的日期时间表示标准，如 2026-09-22T10:30:00+08:00。
  • ISO 周（ISO Week）：以周一为一周起点、包含当年第一个周四的那一周为第 1 周。
  • 季度（Quarter）：date_trunc('quarter', ...) 支持的截断单位。
  • 世纪（Century）：EXTRACT(CENTURY ...) 返回 21 表示 2001–2100 年。

语法与用法 ​

sql
-- 输出格式化
to_char(value, pattern)

-- 输入解析
to_date(text, pattern)
to_timestamp(text, pattern)

-- 字段提取
EXTRACT(field FROM source)
date_part(field, source)

-- 精度截断
date_trunc(field, source)

-- 生成序列（报表补齐日期用）
generate_series(start, stop, step)

to_char 的模式串可以拼接普通文本，但普通文本中的字母有被误认为模式字符的风险，需要用双引号包裹。

sql
SELECT to_char(TIMESTAMP '2026-09-22 10:30:45.123', 'YYYY年MM月DD日 HH24:MI:SS');
text
      to_char
-------------------
 2026年09月22日 10:30:45
sql
-- 双引号把普通文本固定下来，避免被当作模式字符
SELECT to_char(TIMESTAMP '2026-09-22 10:30:45', 'YYYY"年"MM"月"DD"日"');
text
       to_char
---------------------
 2026年09月22日

详细说明 ​

to_char 模式串完整对照表 ​

日期时间部分的模式字符：

模式含义示例输出
YYYY4 位年份2026
YYY后 3 位年份026
YY后 2 位年份26
Y最后 1 位年份6
IYYYISO 8601 周编号年2026
IYYISO 周编号年后 3 位026
MM月份（01–12）09
MON月份缩写SEP
MONTH月份全称SEPTEMBER
DD月内日（01–31）22
DDD年内日（001–366）265
DAY星期全称TUESDAY
DY星期缩写TUE
D星期序号（1–7，周日为 1）3
IDISO 星期序号（1–7，周一为 1）2
W月内周数（1–5）4
WW年内周数（1–53）38
IWISO 8601 周数（01–53）39
Q季度（1–4）3
CC世纪（2 位）21

时间部分的模式字符：

模式含义示例输出
HH2424 小时制（00–23）10
HH1212 小时制（01–12）10
HH同 HH1210
MI分钟（00–59）30
SS秒（00–59）45
SSSS / SSSSS当日已过秒数（0–86399）37845
MS毫秒（000–999）123
US微秒（000000–999999）123456
AM / PM上下午标记AM
TZ时区缩写CST（有歧义，慎用）
OFUTC 偏移+08
TZH时区小时部分+08
TZM时区分钟部分00

一个把上述模式串全部用上的实测对比：

sql
SELECT
  to_char(ts, 'YYYY-MM-DD')          AS iso_date,
  to_char(ts, 'YYYY"Q"Q')            AS year_quarter,
  to_char(ts, 'IYYY-"W"IW')          AS iso_week,
  to_char(ts, 'IW')                  AS week_no,
  to_char(ts, 'DAY')                 AS day_name,
  to_char(ts, 'ID')                  AS iso_dow,
  to_char(ts, 'DDD')                 AS day_of_year,
  to_char(ts, 'HH24:MI:SS.US')       AS time_full,
  to_char(ts, 'TZH:TZM')             AS utc_offset
FROM (SELECT TIMESTAMPTZ '2026-09-22 10:30:45.123456+08' AS ts) t;
text
  iso_date  | year_quarter | iso_week  | week_no | day_name | iso_dow | day_of_year |     time_full      | utc_offset
------------+--------------+-----------+---------+----------+---------+-------------+--------------------+------------
 2026-09-22 | 2026Q3       | 2026-W39  | 39      | TUESDAY  | 2       | 265         | 10:30:45.123456    | +08

注意 DAY 输出是定长补空格到 9 个字符，如果需要去掉尾部空格，用 FM 前缀（Fill Mode）：

sql
SELECT
  to_char(TIMESTAMP '2026-09-22', 'DAY')    AS padded,
  to_char(TIMESTAMP '2026-09-22', 'FMDAY')  AS trimmed,
  '[' || to_char(TIMESTAMP '2026-09-22', 'DAY') || ']'   AS with_pad,
  '[' || to_char(TIMESTAMP '2026-09-22', 'FMDAY') || ']' AS without_pad;
text
  padded   | trimmed |   with_pad    | without_pad
-----------+---------+---------------+-------------
 TUESDAY   | TUESDAY | [TUESDAY   ]  | [TUESDAY]

FM 还可以抑制数字前导零，如 FMMM 输出 9 而非 09。整个模式串前加一次 FM 即可作用于全部模式。

sql
SELECT
  to_char(TIMESTAMP '2026-09-02 08:05:00', 'MM-DD HH24:MI')   AS normal,
  to_char(TIMESTAMP '2026-09-02 08:05:00', 'FMMM-DD HH24:MI') AS fm_mode;
text
   normal    |  fm_mode
-------------+------------
 09-02 08:05 | 9-02 08:05

to_date 与 to_timestamp 解析 ​

解析函数把外部文本转成日期时间值。两者行为差异在于返回类型和时区处理。

sql
SELECT
  to_date('2026/09/22', 'YYYY/MM/DD')                        AS d,
  to_timestamp('2026-09-22 10:30:45', 'YYYY-MM-DD HH24:MI:SS') AS ts;
text
     d      |              ts
------------+-------------------------------
 2026-09-22 | 2026-09-22 10:30:45+08

to_timestamp(text, text) 返回 TIMESTAMPTZ，解析时按会话 TimeZone 解释输入文本（无偏移时）。

sql
SET TimeZone TO 'Asia/Shanghai';
SELECT to_timestamp('2026-09-22 10:30:45', 'YYYY-MM-DD HH24:MI:SS') AS in_shanghai;

SET TimeZone TO 'UTC';
SELECT to_timestamp('2026-09-22 10:30:45', 'YYYY-MM-DD HH24:MI:SS') AS in_utc;
text
      in_shanghai
----------------------------
 2026-09-22 10:30:45+08
(1 row)

        in_utc
------------------------
 2026-09-22 10:30:45+00
(1 row)

同一个文本被解释成了不同的绝对时刻——相差 8 小时。这是解析函数的头号陷阱：to_timestamp(text, text) 依赖会话时区，结果不稳定。

sql
-- 错误！会话时区变化会导致解析出不同的绝对时刻
INSERT INTO events (occurred_at)
VALUES (to_timestamp('2026-09-22 10:30:45', 'YYYY-MM-DD HH24:MI:SS'));

正确做法是把偏移写进文本，并在模式串里用 TZH:TZM 显式接收：

sql
-- 正确：偏移随文本一起解析，结果与会话时区无关
SELECT to_timestamp('2026-09-22 10:30:45+08', 'YYYY-MM-DD HH24:MI:SS TZH:TZM') AS explicit_offset;
text
     explicit_offset
------------------------
 2026-09-22 10:30:45+08

解析失败的行为也需要注意。to_date 对格式不匹配的输入往往抛出异常：

sql
SELECT to_date('2026-13-45', 'YYYY-MM-DD');
text
ERROR:  date/time field value out of range: "2026-13-45"
LINE 1: SELECT to_date('2026-13-45', 'YYYY-MM-DD');

但某些"宽松"输入会被静默接受：

sql
-- 注意：多余字符被忽略，没有报错
SELECT
  to_date('2026-09-22 垃圾字符', 'YYYY-MM-DD') AS ignored_tail,
  to_date('2026-09-22', 'YYYY-MM-DD HH24:MI:SS') AS missing_time;
text
 ignored_tail | missing_time
--------------+--------------
 2026-09-22   | 2026-09-22

to_date 的返回类型是 DATE，即使模式串里写了时间部分也会被丢弃。需要保留时间时用 to_timestamp。

EXTRACT 与 date_part ​

EXTRACT 是取出单个字段的标准写法，配合 date_trunc 可以完成绝大多数分组统计。

sql
SELECT
  EXTRACT(YEAR    FROM TIMESTAMPTZ '2026-09-22 10:30:45.123456+08') AS yr,
  EXTRACT(MONTH   FROM TIMESTAMPTZ '2026-09-22 10:30:45.123456+08') AS mon,
  EXTRACT(DAY     FROM TIMESTAMPTZ '2026-09-22 10:30:45.123456+08') AS dy,
  EXTRACT(HOUR    FROM TIMESTAMPTZ '2026-09-22 10:30:45.123456+08') AS hr,
  EXTRACT(MINUTE  FROM TIMESTAMPTZ '2026-09-22 10:30:45.123456+08') AS mi,
  EXTRACT(SECOND  FROM TIMESTAMPTZ '2026-09-22 10:30:45.123456+08') AS sec,
  EXTRACT(EPOCH   FROM TIMESTAMPTZ '2026-09-22 10:30:45.123456+08') AS epoch,
  EXTRACT(DOW     FROM TIMESTAMPTZ '2026-09-22 10:30:45+08')         AS dow,
  EXTRACT(ISODOW  FROM TIMESTAMPTZ '2026-09-22 10:30:45+08')         AS isodow,
  EXTRACT(QUARTER FROM TIMESTAMPTZ '2026-09-22 10:30:45+08')         AS qtr;
text
  yr  | mon | dy | hr | mi |       sec        |       epoch       | dow | isodow | qtr
------+-----+----+----+----+------------------+-------------------+-----+--------+-----
 2026 |   9 | 22 | 10 | 30 | 45.123456        | 1789007445.123456 |   2 |      2 |   3

EXTRACT(SECOND ...) 返回带小数秒的 numeric，这是它优于 date_part 的地方——date_part 返回 double precision，大时间戳下可能有精度损失。

sql
SELECT
  EXTRACT(SECOND FROM TIMESTAMPTZ '2026-09-22 10:30:45.123456+08') AS extract_second,
  date_part('second', TIMESTAMPTZ '2026-09-22 10:30:45.123456+08')  AS date_part_second,
  pg_typeof(EXTRACT(SECOND FROM now())) AS extract_type,
  pg_typeof(date_part('second', now()))  AS date_part_type;
text
 extract_second | date_part_second | extract_type | date_part_type
----------------+------------------+--------------+----------------
 45.123456      |        45.123456 | numeric      | double precision

EXTRACT(EPOCH ...) 把时间戳转成 Unix 秒数（自 1970-01-01 UTC），是与外部系统对接时最常用的转换。

sql
-- 与 PHP time() / Java System.currentTimeMillis() 对接
SELECT
  EXTRACT(EPOCH FROM TIMESTAMPTZ '2026-09-22 10:30:45+08')::BIGINT        AS unix_seconds,
  (EXTRACT(EPOCH FROM TIMESTAMPTZ '2026-09-22 10:30:45+08') * 1000)::BIGINT AS unix_millis;
text
 unix_seconds |  unix_millis
--------------+---------------
   1789007445 | 1789007445123

DOW 与 ISODOW 的区别是分组统计中的经典坑：

字段范围周日起点说明
DOW0–6周日 = 0PostgreSQL 扩展，非 SQL 标准
ISODOW1–7周一 = 1ISO 8601 标准
DAY 在 to_char1–7周日 = 1又一套编号，最容易记混
sql
-- 同一批日期，三套星期编号结果不同
SELECT
  to_char(d, 'YYYY-MM-DD')  AS dt,
  to_char(d, 'DAY')         AS name,
  EXTRACT(DOW FROM d)       AS dow,
  EXTRACT(ISODOW FROM d)    AS isodow,
  to_char(d, 'D')           AS to_char_d,
  to_char(d, 'ID')          AS to_char_id
FROM generate_series(DATE '2026-09-20', DATE '2026-09-26', INTERVAL '1 day') AS d;
text
     dt     |   name    | dow | isodow | to_char_d | to_char_id
------------+-----------+-----+--------+-----------+------------
 2026-09-20 | SUNDAY    |   0 |      7 | 1         | 7
 2026-09-21 | MONDAY    |   1 |      1 | 2         | 1
 2026-09-22 | TUESDAY   |   2 |      2 | 3         | 2
 2026-09-23 | WEDNESDAY |   3 |      3 | 4         | 3
 2026-09-24 | THURSDAY  |   4 |      4 | 5         | 4
 2026-09-25 | FRIDAY    |   5 |      5 | 6         | 5
 2026-09-26 | SATURDAY  |   6 |      6 | 7         | 6

做「周一到周日」报表时务必用 ISODOW 或 ID，否则周日会跑到行首。

date_trunc 截断 ​

date_trunc 把时间向下取整到指定精度，是分组统计的主力函数。

sql
SELECT
  date_trunc('year',    TIMESTAMPTZ '2026-09-22 10:30:45.123456+08') AS yr,
  date_trunc('quarter', TIMESTAMPTZ '2026-09-22 10:30:45.123456+08') AS qtr,
  date_trunc('month',   TIMESTAMPTZ '2026-09-22 10:30:45.123456+08') AS mon,
  date_trunc('week',    TIMESTAMPTZ '2026-09-22 10:30:45.123456+08') AS wk,
  date_trunc('day',     TIMESTAMPTZ '2026-09-22 10:30:45.123456+08') AS dy,
  date_trunc('hour',    TIMESTAMPTZ '2026-09-22 10:30:45.123456+08') AS hr,
  date_trunc('minute',  TIMESTAMPTZ '2026-09-22 10:30:45.123456+08') AS mi,
  date_trunc('second',  TIMESTAMPTZ '2026-09-22 10:30:45.123456+08') AS sec;
text
          yr           |         qtr          |         mon          |         wk           |         dy           |         hr           |         mi           |         sec
-----------------------+----------------------+----------------------+----------------------+----------------------+----------------------+----------------------+----------------------
 2026-01-01 00:00:00+08| 2026-07-01 00:00:00+08| 2026-09-01 00:00:00+08| 2026-09-21 00:00:00+08| 2026-09-22 00:00:00+08| 2026-09-22 10:00:00+08| 2026-09-22 10:30:00+08| 2026-09-22 10:30:45+08

注意 date_trunc('week', ...) 返回的是周一（ISO 语义），不是周日。返回值类型与输入类型一致：输入 TIMESTAMPTZ 得到 TIMESTAMPTZ，输入 DATE 得到 TIMESTAMPTZ（DATE 会被提升）。

sql
SELECT
  pg_typeof(date_trunc('day', TIMESTAMPTZ '2026-09-22 10:30:45+08')) AS from_tstz,
  pg_typeof(date_trunc('day', DATE '2026-09-22'))                    AS from_date,
  pg_typeof(date_trunc('day', TIMESTAMP '2026-09-22 10:30:45'))      AS from_ts;
text
 from_tstz | from_date | from_ts
-----------+-----------+-----------
 timestamp with time zone | timestamp with time zone | timestamp without time zone

date_trunc 支持 milliseconds 与 microseconds，这是 PostgreSQL 相对 MySQL 的扩展：

sql
SELECT
  date_trunc('milliseconds', TIMESTAMPTZ '2026-09-22 10:30:45.123456+08') AS ms,
  date_trunc('microseconds', TIMESTAMPTZ '2026-09-22 10:30:45.123456+08') AS us;
text
            ms             |            us
---------------------------+---------------------------
 2026-09-22 10:30:45.123+08| 2026-09-22 10:30:45.123456+08

ISO 8601 与 ISO 周体系 ​

ISO 8601 规定：一周从周一开始，第 1 周是包含当年第一个周四的那一周。这意味着 12 月 31 日可能属于下一年的第 1 周，1 月 1 日也可能属于上一年的第 53 周。

sql
-- 跨年边界的 ISO 周归属
SELECT
  d::DATE                                            AS dt,
  to_char(d, 'IYYY')                                 AS iso_year,
  to_char(d, 'IW')                                   AS iso_week,
  EXTRACT(ISOYEAR FROM d)                            AS extract_isoyear,
  EXTRACT(WEEK    FROM d)                            AS extract_week
FROM (VALUES
  (DATE '2025-12-28'),  -- 周日，属于 2025 年第 52 周
  (DATE '2025-12-29'),  -- 周一，属于 2026 年第 1 周
  (DATE '2026-01-01'),  -- 周四，属于 2026 年第 1 周
  (DATE '2027-01-01'),  -- 周五，属于 2026 年第 53 周
  (DATE '2027-01-04')   -- 周一，属于 2027 年第 1 周
) AS t(d);
text
     dt     | iso_year | iso_week | extract_isoyear | extract_week
------------+----------+----------+-----------------+--------------
 2025-12-28 | 2025     | 52       |            2025 |           52
 2025-12-29 | 2026     | 01       |            2026 |            1
 2026-01-01 | 2026     | 01       |            2026 |            1
 2027-01-01 | 2026     | 53       |            2026 |           53
 2027-01-04 | 2027     | 01       |            2027 |            1

关键点：ISO 周必须与 ISO 年配套使用。如果只取 to_char(d, 'YYYY-"W"IW')，2025-12-29 会输出 2025-W01，这是错误的——2025 年第 1 周其实是 2024 年底。

sql
-- 错误！年份用 YYYY 而周数用 IW，跨年时会得到不存在的周
SELECT to_char(DATE '2025-12-29', 'YYYY-"W"IW') AS wrong,   -- 2025-W01，但 2025 年第 1 周不是这天
       to_char(DATE '2025-12-29', 'IYYY-"W"IW') AS correct; -- 2026-W01
text
   wrong    |  correct
------------+-----------
 2025-W01   | 2026-W01

周数的三种编号也要区分：

sql
SELECT
  d::DATE                      AS dt,
  to_char(d, 'WW')             AS ww,      -- 年内周数，1 月 1 日起每 7 天一周
  to_char(d, 'IW')             AS iw,      -- ISO 周数
  to_char(d, 'W')              AS w,       -- 月内周数
  to_char(d, 'YYYY-"W"WW')     AS yyyy_ww,
  to_char(d, 'IYYY-"W"IW')     AS iyyy_iw
FROM generate_series(DATE '2026-01-01', DATE '2026-01-15', INTERVAL '1 day') AS d;
text
     dt     | ww | iw | w | yyyy_ww | iyyy_iw
------------+----+----+---+---------+---------
 2026-01-01 | 01 | 01 | 1 | 2026-W01| 2026-W01
 2026-01-02 | 01 | 01 | 1 | 2026-W01| 2026-W01
 2026-01-03 | 01 | 01 | 1 | 2026-W01| 2026-W01
 2026-01-04 | 01 | 01 | 1 | 2026-W01| 2026-W01
 2026-01-05 | 02 | 02 | 1 | 2026-W02| 2026-W02
 ...

输出为 JSON 与字符串拼接 ​

导出给前端时通常需要把日期格式化成字符串再嵌入 JSON。to_char 的返回值本身就是 text，to_json 系列会自动加引号。

sql
SELECT
  to_json(to_char(TIMESTAMPTZ '2026-09-22 10:30:45+08', 'YYYY-MM-DD"T"HH24:MI:SSOF'))  AS json_str,
  to_json(TIMESTAMPTZ '2026-09-22 10:30:45+08')                                        AS json_native,
  jsonb_build_object(
    'created_at', to_char(TIMESTAMPTZ '2026-09-22 10:30:45+08', 'YYYY-MM-DD HH24:MI:SS'),
    'epoch',      EXTRACT(EPOCH FROM TIMESTAMPTZ '2026-09-22 10:30:45+08')::BIGINT
  )                                                                                     AS payload;
text
               json_str                |           json_native            |                      payload
---------------------------------------+----------------------------------+---------------------------------------------------
 "2026-09-22T10:30:45+08"              | "2026-09-22T10:30:45.000000+08:00"| {"epoch": 1789007445, "created_at": "2026-09-22 10:30:45"}

to_json(timestamptz) 自动产出符合 ISO 8601 的字符串，通常比手工拼模式串更可靠，因为它不受会话 DateStyle 影响：

sql
-- DateStyle 影响直接输出，但不影响 to_char 与 to_json 的固定模式
SET DateStyle TO 'German, DMY';
SELECT
  TIMESTAMPTZ '2026-09-22 10:30:45+08'                                    AS direct_output,
  to_char(TIMESTAMPTZ '2026-09-22 10:30:45+08', 'YYYY-MM-DD HH24:MI:SS')  AS via_to_char,
  to_json(TIMESTAMPTZ '2026-09-22 10:30:45+08')                           AS via_to_json;
RESET DateStyle;
text
 direct_output |     via_to_char     |              via_to_json
---------------+---------------------+---------------------------------------
 22.09.2026 10:30:45+08 | 2026-09-22 10:30:45 | "2026-09-22T10:30:45.000000+08:00"

DateStyle 是会话级参数，直接输出格式随它变化；to_char 与 to_json 输出稳定——这是对外接口必须走格式化函数的原因。

实战示例 ​

下面用一个订单报表场景，从建表到按周、按月、按季度统计，完整走一遍。

第 1 步：建表并准备数据 ​

sql
CREATE TABLE orders (
  id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_no    TEXT        NOT NULL UNIQUE,
  amount      NUMERIC(12, 2) NOT NULL,
  -- 下单时刻，用 TIMESTAMPTZ 记录绝对时间
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- 显式指定时区，保证数据含义与运行环境无关
INSERT INTO orders (order_no, amount, created_at)
VALUES
  ('SO-202609-001',  199.00, TIMESTAMPTZ '2026-09-21 09:15:00+08'),
  ('SO-202609-002',  899.00, TIMESTAMPTZ '2026-09-21 21:40:00+08'),
  ('SO-202609-003',  150.50, TIMESTAMPTZ '2026-09-22 10:30:45+08'),
  ('SO-202609-004', 2380.00, TIMESTAMPTZ '2026-09-22 23:05:00+08'),
  ('SO-202609-005',   45.00, TIMESTAMPTZ '2026-09-23 00:10:00+08'),
  ('SO-202610-001',  760.00, TIMESTAMPTZ '2026-10-01 11:00:00+08'),
  ('SO-202611-001',  520.00, TIMESTAMPTZ '2026-11-15 16:20:00+08'),
  ('SO-202612-001',  310.00, TIMESTAMPTZ '2026-12-31 23:30:00+08'),
  ('SO-202701-001',  980.00, TIMESTAMPTZ '2027-01-01 08:00:00+08');
text
INSERT 0 9

第 2 步：按月统计并格式化展示 ​

sql
SELECT
  to_char(date_trunc('month', created_at), 'YYYY-MM') AS month,
  COUNT(*)                                            AS order_cnt,
  SUM(amount)                                         AS total_amount
FROM orders
GROUP BY date_trunc('month', created_at)
ORDER BY date_trunc('month', created_at);
text
  month  | order_cnt | total_amount
---------+-----------+--------------
 2026-09 |         5 |      3673.50
 2026-10 |         1 |       760.00
 2026-11 |         1 |       520.00
 2026-12 |         1 |       310.00
 2027-01 |         1 |       980.00
(5 rows)

这里 GROUP BY date_trunc('month', created_at) 会按会话时区换算后再截断。如果业务要求固定按北京时间统计，必须显式转换：

sql
SELECT
  to_char(date_trunc('month', created_at AT TIME ZONE 'Asia/Shanghai'), 'YYYY-MM') AS month,
  COUNT(*) AS order_cnt
FROM orders
GROUP BY date_trunc('month', created_at AT TIME ZONE 'Asia/Shanghai')
ORDER BY 1;

第 3 步：按 ISO 周统计，验证跨周归属 ​

sql
SELECT
  to_char(created_at, 'IYYY-"W"IW')  AS iso_week,
  to_char(created_at, 'FMDAY')       AS day_name,
  COUNT(*)                           AS order_cnt
FROM orders
GROUP BY 1, 2
ORDER BY 1, 2;
text
 iso_week  | day_name | order_cnt
-----------+----------+-----------
 2026-W39  | MONDAY   |         2
 2026-W39  | TUESDAY  |         2
 2026-W39  | WEDNESDAY|         1
 2026-W40  | THURSDAY |         1
 2026-W46  | SUNDAY   |         1
 2026-W53  | THURSDAY |         1
 2027-W01  | FRIDAY   |         1
(7 rows)

注意 2026-12-31 被归到 2026-W53，而 2027-01-01 被归到 2027-W01——这正是 ISO 周体系的规则。

第 4 步：按季度与年度汇总 ​

sql
SELECT
  to_char(created_at, 'YYYY"年"Q"季度"')  AS quarter_label,
  date_trunc('quarter', created_at)       AS quarter_start,
  COUNT(*)                                AS order_cnt,
  SUM(amount)                             AS total_amount
FROM orders
GROUP BY 1, 2
ORDER BY 2;
text
  quarter_label  |    quarter_start     | order_cnt | total_amount
-----------------+----------------------+-----------+--------------
 2026年3季度     | 2026-07-01 00:00:00+08 |         5 |      3673.50
 2026年4季度     | 2026-10-01 00:00:00+08 |         3 |      1590.00
 2027年1季度     | 2027-01-01 00:00:00+08 |         1 |       980.00
(3 rows)

第 5 步：生成连续日期序列，补齐无数据的日子 ​

报表经常要求「没有订单的日期也显示为 0」。generate_series 生成日期骨架后左连接即可。

sql
WITH span AS (
  SELECT generate_series(
           date_trunc('day', TIMESTAMPTZ '2026-09-21 00:00:00+08'),
           date_trunc('day', TIMESTAMPTZ '2026-09-24 00:00:00+08'),
           INTERVAL '1 day'
         ) AS day_start
)
SELECT
  to_char(s.day_start AT TIME ZONE 'Asia/Shanghai', 'YYYY-MM-DD') AS day,
  to_char(s.day_start AT TIME ZONE 'Asia/Shanghai', 'FMDAY')      AS day_name,
  COALESCE(COUNT(o.id), 0)                                        AS order_cnt,
  COALESCE(SUM(o.amount), 0)                                      AS total_amount
FROM span s
LEFT JOIN orders o
       ON o.created_at >= s.day_start
      AND o.created_at <  s.day_start + INTERVAL '1 day'
GROUP BY s.day_start
ORDER BY s.day_start;
text
    day     | day_name | order_cnt | total_amount
------------+----------+-----------+--------------
 2026-09-21 | MONDAY   |         2 |      1098.00
 2026-09-22 | TUESDAY  |         2 |      2530.50
 2026-09-23 | WEDNESDAY|         1 |        45.00
 2026-09-24 | THURSDAY |         0 |         0.00
(4 rows)

注意日期边界用 >= 当天起点 AND < 次日起点 的半开区间，而不是 BETWEEN——半开区间能正确处理带小数秒的时间戳，也更容易命中索引。

sql
-- 不推荐：BETWEEN 是闭区间，会漏掉当天 23:59:59.000001 之后的数据
-- SELECT * FROM orders
-- WHERE created_at BETWEEN '2026-09-22 00:00:00+08' AND '2026-09-22 23:59:59+08';

-- 推荐：半开区间，边界清晰且可利用索引
SELECT order_no, amount, created_at
FROM orders
WHERE created_at >= TIMESTAMPTZ '2026-09-22 00:00:00+08'
  AND created_at <  TIMESTAMPTZ '2026-09-23 00:00:00+08';
text
   order_no    | amount  |        created_at
---------------+---------+------------------------
 SO-202609-003 |  150.50 | 2026-09-22 10:30:45+08
 SO-202609-004 | 2380.00 | 2026-09-22 23:05:00+08
(2 rows)

第 6 步：导出 JSON 报表 ​

sql
SELECT
  jsonb_pretty(
    jsonb_agg(
      jsonb_build_object(
        'month',        to_char(date_trunc('month', created_at), 'YYYY-MM'),
        'order_cnt',    agg.cnt,
        'total_amount', agg.total
      )
      ORDER BY agg.month_start
    )
  ) AS report
FROM (
  SELECT
    date_trunc('month', created_at) AS month_start,
    COUNT(*)                        AS cnt,
    SUM(amount)                     AS total
  FROM orders
  GROUP BY 1
) agg
JOIN orders ON date_trunc('month', orders.created_at) = agg.month_start
GROUP BY agg.month_start, agg.cnt, agg.total;
text
                    report
-----------------------------------------------
 [
     {
         "month": "2026-09",
         "order_cnt": 5,
         "total_amount": 3673.50
     },
     ...
 ]

第 7 步：清理环境 ​

sql
DROP TABLE orders;
text
DROP TABLE

注意事项 ​

  • 模式串里的普通字母必须用双引号包裹：'YYYY年MM月' 中的中文安全，但 'YYYY-MM-DDTHH24' 里的 T 会被当作模式字符。不确定时一律写 "T"。

  • to_char 不受会话 DateStyle 影响，但直接输出受：对外接口一律走 to_char 或 to_json，不要让裸的时间戳参与字符串拼接。

  • to_timestamp(text, text) 依赖会话 TimeZone：这是不稳定行为。必须解析外部时间时，把偏移写进文本并用 TZH:TZM 显式接收。

  • EXTRACT(SECOND) 返回 numeric 小数，而 date_part 返回 double precision。做等值比较时要留意类型差异：

    sql
    -- 这个比较恒为 false，因为 EXTRACT 小数部分不为 0
SELECT EXTRACT(SECOND FROM TIMESTAMPTZ '2026-09-22 10:30:45+08') = 45 AS is_45;
    text
     is_45
-------
 f

    正确写法是先取整：EXTRACT(SECOND FROM ts)::INT = 45 或 date_trunc('second', ts)。

  • ISO 周必须用 IYYY 配 IW，不能混用 YYYY 与 IW，否则跨年时会输出不存在的周。

  • 三套星期编号不要混用：DOW（周日=0）、ISODOW（周一=1）、to_char(..., 'D')（周日=1）。做周报表统一用 ISODOW。

  • date_trunc 的返回值类型跟随输入：DATE 输入会提升为 TIMESTAMPTZ，然后参与会话时区换算，可能产生意外偏移。

    sql
    SET TimeZone TO 'America/New_York';
-- DATE 被当作本地零点，截断结果带 -04/-05 偏移
SELECT date_trunc('day', DATE '2026-09-22') AS truncated_date;
RESET TimeZone;
    text
          truncated_date
-------------------------
 2026-09-22 00:00:00-04

  • to_date 的返回类型是 DATE，模式串中的时间部分会被丢弃。需要时间精度时用 to_timestamp 或 ::TIMESTAMP。

  • to_date 会静默忽略尾部多余字符，不会报错。清洗导入数据时不要依赖它做格式校验，应配合正则或 IS JSON 之类的显式检查。

  • generate_series 的时间步长支持 INTERVAL，但不支持「加一个月」的语义统一：INTERVAL '1 month' 在月末会夹取，生成序列时可能重复或跳变。

    sql
    SELECT d::DATE
FROM generate_series(DATE '2026-01-31', DATE '2026-05-31', INTERVAL '1 month') AS d;
    text
        d
------------
 2026-01-31
 2026-02-28
 2026-03-28
 2026-04-28

  需要严格月末序列时，改用按月递增的整数序号再计算日期。

## 最佳实践

- **存储层一律用原生日期时间类型（`TIMESTAMPTZ` / `DATE` / `INTERVAL`），不要把时间存成字符串**：字符串存储无法做范围查询优化，也无法保证格式一致。格式化只在输出边界做。

- **格式化的唯一出口是 `to_char` 或 `to_json`**：把格式化集中在视图或应用层，不要让同一个字段在多个查询里用不同模式串。

- **对外接口统一用 ISO 8601**：建议模式串 `'YYYY-MM-DD"T"HH24:MI:SS.USOF'`，或直接 `to_json(timestamptz)`。ISO 8601 是唯一无歧义的跨系统表示。

- **周报表统一用 ISO 周体系**：

  ```sql
  SELECT
    to_char(created_at, 'IYYY-"W"IW') AS iso_week,
    MIN(created_at)                   AS week_start_hint
  FROM orders
  GROUP BY 1
  ORDER BY 1;

  • 月度统计用 date_trunc('month', ...) 而不是字符串截取：substring(to_char(created_at, 'YYYY-MM-DD'), 1, 7) 既慢又绕，而且无法利用时间索引。

  • 日期范围查询用半开区间：

    sql
    -- 推荐写法
WHERE created_at >= :start_date
  AND created_at <  :end_date_exclusive

    这样调用方只需传两个日期，无需处理"当天最后一秒"这个永远写不对的值。

  • 报表补齐日期用 generate_series + LEFT JOIN，而不是在应用层循环补零。数据库里一次算完，减少网络往返。

  • 按业务时区统计时先 AT TIME ZONE 再 date_trunc：

    sql
    SELECT to_char(date_trunc('day', created_at AT TIME ZONE 'Asia/Shanghai'), 'YYYY-MM-DD') AS biz_day,
       COUNT(*)
FROM orders
GROUP BY 1
ORDER BY 1;

    顺序反了会先按会话时区截断，跨零点订单会落到错误的日子。

  • 导入外部日期文本时先做格式校验：用 to_date 配合异常处理，或先用正则 ~ '^\d{4}-\d{2}-\d{2}$' 筛选，避免静默接受脏数据。

  • 为 to_char 的高频模式串建立表达式索引时要谨慎：to_char 依赖会话 lc_time，不是 IMMUTABLE，无法直接建索引。需要按天/月分组时改用 date_trunc（对 TIMESTAMP 是 IMMUTABLE）建表达式索引。

    sql
    -- 错误！to_char 不是 IMMUTABLE，无法建索引
-- CREATE INDEX idx_orders_day ON orders (to_char(created_at, 'YYYY-MM-DD'));

-- 正确：date_trunc 对 TIMESTAMP 是 IMMUTABLE
CREATE INDEX idx_orders_day
    ON orders (date_trunc('day', created_at AT TIME ZONE 'UTC'));

下一节

继续学习：日期时间类型选型指南

参考链接 ​