PHP小码农

Appearance

EXTRACT / DATE_PART ​

EXTRACT 是把日期时间「拆开」的最常用工具：从时间戳里取出年、月、日、时、分、秒、季度、周数、时区偏移，甚至 Unix 时间戳。它有一个几乎等价的双胞胎 date_part()，两者在任何正常查询里结果都一样，却在返回类型和参数顺序上存在关键差异——这个差异会在 WHERE 条件、索引匹配、UNION 类型推断中悄悄改变执行计划。本节把两个函数的全部可用字段列成表，并逐条演示它们的差异与陷阱。

前置知识

阅读本节前，建议先了解：NOW / CURRENT_DATE / CURRENT_TIME

基础概念 ​

EXTRACT 的输入是「一个日期时间值」加「一个字段名」，输出是「该字段的数值」。理解它需要先掌握三组概念：字段族（年/月/日/时分秒/周/时区）、字段命名法（Gregorian 与 ISO 8601 两套并行）、以及返回类型。

概念术语说明
字段field要抽取的时间组成部分，如 year、month
表达式形式expression syntaxEXTRACT(field FROM source)，SQL 标准
函数形式function syntaxdate_part('field', source)，PostgreSQL 扩展
公历字段Gregorian fielddow / week / year，与日常日历一致
ISO 8601 字段ISO 8601 fieldisodow / isoyear / week，与 ISO 周历一致
返回类型return typeextract → numeric；date_part → double precision
EpochUnix Epoch自 1970-01-01 00:00:00 UTC 起的秒数
时区转换time zone rotationtimestamptz 先按会话时区渲染再抽字段

核心差异（最容易踩的坑）：

对比项EXTRACT(field FROM src)date_part('field', src)
参数顺序字段在前，用 FROM 连接字段是第一个字符串参数
字段写法关键字，不加引号字符串字面量，要加引号
返回类型numericdouble precision
能否直接 ::int可以，numeric → int 是四舍五入可以，double → int 是四舍五入
索引表达式可用于表达式索引可用于表达式索引
标准符合度SQL 标准PostgreSQL 专有
sql
-- 同一语义，两种写法的返回类型不同
SELECT
  pg_typeof(EXTRACT(YEAR FROM TIMESTAMP '2026-09-24 10:30:00'))   AS extract_type,
  pg_typeof(date_part('year', TIMESTAMP '2026-09-24 10:30:00'))  AS date_part_type;
text
 extract_type |   date_part_type
--------------+--------------------
 numeric      | double precision
(1 row)

这个差异带来的实际后果是：extract(...)::text 输出 2026 而 date_part(...)::text 输出 2026——看似一样，但当字段带小数时（如 epoch、second）输出精度就不同了。更关键的是 UNION / CASE 的类型推断：混用两个函数会让结果类型在 numeric 与 double precision 之间摇摆。

选型流程：

text
你需要抽取时间字段吗？
  ├─ 需要，且追求 SQL 标准可移植性 → EXTRACT(field FROM src)
  ├─ 需要，且习惯 MySQL / Oracle 风格 → date_part('field', src)
  └─ 需要整体换算成数值（秒/天）→ extract(epoch from ts) 或直接相减

一条硬规则：同一个项目里只选一种。混用会让代码审查与类型推断都变复杂。

语法与用法 ​

sql
-- 表达式形式（推荐）
EXTRACT(field FROM source)

-- 函数形式（等价，注意引号）
date_part('field', source)

-- source 可以是：date / time / timestamp / timestamptz / interval

最小可运行示例：

sql
SELECT
  EXTRACT(YEAR   FROM TIMESTAMP '2026-09-24 10:30:45.123456') AS y,
  EXTRACT(MONTH  FROM TIMESTAMP '2026-09-24 10:30:45.123456') AS m,
  EXTRACT(DAY    FROM TIMESTAMP '2026-09-24 10:30:45.123456') AS d,
  EXTRACT(HOUR   FROM TIMESTAMP '2026-09-24 10:30:45.123456') AS h,
  EXTRACT(MINUTE FROM TIMESTAMP '2026-09-24 10:30:45.123456') AS mi,
  EXTRACT(SECOND FROM TIMESTAMP '2026-09-24 10:30:45.123456') AS s;
text
  y   | m  | d  | h  | mi |     s
------+----+----+----+----+----------
 2026 |  9 | 24 | 10 | 30 | 45.123456
(1 row)

注意 SECOND 返回带小数，而年/月/日/时/分都是整数。

准备本节贯穿使用的示例表（在 demo 库中执行）：

sql
-- 订单表：用于演示按季度/周/小时等维度分组统计
CREATE TABLE orders (
  id          INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_no    TEXT           NOT NULL,
  user_id     INTEGER        NOT NULL,
  amount      NUMERIC(12, 2) NOT NULL,
  paid_at     TIMESTAMPTZ    NOT NULL,
  created_at  TIMESTAMPTZ    NOT NULL
);

-- 造数：覆盖 2025-01-01 至 2026-09-30，共 800 行
INSERT INTO orders (order_no, user_id, amount, paid_at, created_at)
SELECT
  'NO' || lpad(i::text, 8, '0'),
  1 + (i % 200),
  round((random() * 900 + 100)::numeric, 2),
  TIMESTAMPTZ '2025-01-01 00:00:00+08' + (i * 9) * INTERVAL '1 hour',
  TIMESTAMPTZ '2025-01-01 00:00:00+08' + (i * 9) * INTERVAL '1 hour' + INTERVAL '37 minutes'
FROM generate_series(1, 800) AS i;

CREATE INDEX idx_orders_paid_at ON orders (paid_at);
ANALYZE orders;
sql
-- 最小可用：按季度统计订单金额
SELECT
  EXTRACT(YEAR FROM paid_at)::INTEGER AS yr,
  EXTRACT(QUARTER FROM paid_at)::INTEGER AS qtr,
  count(*) AS cnt,
  round(sum(amount), 2) AS total
FROM orders
GROUP BY yr, qtr
ORDER BY yr, qtr;
text
  yr  | qtr | cnt |   total
------+-----+-----+------------
 2025 |   1 |  83 |  41236.90
 2025 |   2 |  91 |  45879.12
 ...
 2026 |   3 |  74 |  37288.44
(7 rows)

详细说明 ​

完整字段清单 ​

EXTRACT 支持的字段按来源类型分为三组。用错来源类型会报错，例如 EXTRACT(HOUR FROM DATE '2026-09-24') 是合法的（date 会先转成 timestamp，小时为 0），但 EXTRACT(YEAR FROM TIME '10:30') 会报错。

表 1：从 timestamp / date / timestamptz 抽取

字段含义示例（2026-09-24 10:30:45.123456）
century世纪21
day日（1–31）24
decade十年段（年份 ÷ 10 取整）202
dow星期几，周日=0 … 周六=64（周四）
doy一年中的第几天（1–366）267
epoch距 1970-01-01 00:00:00 UTC 的秒数1790245845.123456
hour小时（0–23）10
isodowISO 星期几，周一=1 … 周日=74
isoyearISO 8601 周号纪年2026
julian儒略日2461312
microseconds秒内的微秒数（含秒，0–59999999）45123456
millennium千年段3
milliseconds秒内的毫秒数（含秒）45123.456
minute分钟（0–59）30
month月（1–12）9
quarter季度（1–4）3
second秒（含小数）45.123456
timezone相对 UTC 的偏移秒数28800（+08:00）
timezone_hour偏移的小时部分8
timezone_minute偏移的分钟部分0
weekISO 8601 周号（1–53）39
year年2026

表 2：从 interval 抽取

字段含义
century / decade / millennium按 12 个月为一年的近似换算
day天数部分（不含月/时换算）
hour / minute小时/分钟部分
microseconds / milliseconds / second秒的小数部分
month月数部分（含年 × 12）
quarter月数 ÷ 3
year年数部分
epoch总秒数（按 1 月 = 30 天、1 天 = 24 小时换算）
timezone / timezone_hour / timezone_minute间隔中不带时区，返回 0

表 3：从 time / timetz 抽取

字段含义
hour / minute / second / microseconds / milliseconds时分秒各部分
epoch距当日 00:00:00 的秒数
timezone / timezone_hour / timezone_minute仅 timetz 有效
sql
-- 一次性展示最常用的 12 个字段
SELECT
  EXTRACT(CENTURY   FROM TIMESTAMPTZ '2026-09-24 10:30:45.123456+08') AS century,
  EXTRACT(DECADE    FROM TIMESTAMPTZ '2026-09-24 10:30:45.123456+08') AS decade,
  EXTRACT(MILLENNIUM FROM TIMESTAMPTZ '2026-09-24 10:30:45.123456+08') AS millennium,
  EXTRACT(DOY       FROM TIMESTAMPTZ '2026-09-24 10:30:45.123456+08') AS doy,
  EXTRACT(WEEK      FROM TIMESTAMPTZ '2026-09-24 10:30:45.123456+08') AS iso_week,
  EXTRACT(ISOYEAR   FROM TIMESTAMPTZ '2026-09-24 10:30:45.123456+08') AS iso_year,
  EXTRACT(JULIAN    FROM TIMESTAMPTZ '2026-09-24 10:30:45.123456+08') AS julian,
  EXTRACT(EPOCH     FROM TIMESTAMPTZ '2026-09-24 10:30:45.123456+08') AS epoch,
  EXTRACT(TIMEZONE  FROM TIMESTAMPTZ '2026-09-24 10:30:45.123456+08') AS tz_seconds,
  EXTRACT(MICROSECONDS FROM TIMESTAMPTZ '2026-09-24 10:30:45.123456+08') AS micros;
text
 century | decade | millennium | doy | iso_week | iso_year | julian |      epoch       | tz_seconds |  micros
---------+--------+------------+-----+----------+----------+--------+------------------+------------+----------
      21 |    202 |          3 | 267 |       39 |     2026 | 2461312 | 1790245845.123456 |      28800 | 45123456
(1 row)

dow 与 isodow：两套星期编号 ​

这是最容易出错的字段对：

字段周日周一周二周三周四周五周六
dow0123456
isodow7123456
sql
-- 一周七天的两套编号对照
WITH days AS (
  SELECT d::DATE AS dt
  FROM generate_series(DATE '2026-09-21', DATE '2026-09-27', INTERVAL '1 day') AS d
)
SELECT
  dt,
  to_char(dt, 'Dy') AS day_name,
  EXTRACT(DOW FROM dt)::INTEGER    AS dow,
  EXTRACT(ISODOW FROM dt)::INTEGER AS isodow
FROM days
ORDER BY dt;
text
     dt     | day_name | dow | isodow
------------+----------+-----+--------
 2026-09-21 | Mon      |   1 |      1
 2026-09-22 | Tue      |   2 |      2
 2026-09-23 | Wed      |   3 |      3
 2026-09-24 | Thu      |   4 |      4
 2026-09-25 | Fri      |   5 |      5
 2026-09-26 | Sat      |   6 |      6
 2026-09-27 | Sun      |   0 |      7
(7 rows)

业务上「工作日的第几天」必须用 isodow，因为 dow 把周日放在 0 位，dow <= 5 会把周六也算进来：

sql
-- 错误！dow 语义下 0 是周日，条件应为 BETWEEN 1 AND 5
SELECT count(*) FROM orders WHERE EXTRACT(DOW FROM paid_at) BETWEEN 1 AND 5;

-- 正确：isodow 语义下 1=周一、5=周五
SELECT count(*) FROM orders WHERE EXTRACT(ISODOW FROM paid_at) BETWEEN 1 AND 5;

week 与 isoyear：跨年周必须成对使用 ​

week 字段本身就是 ISO 8601 周号（周一为一周之始，含周四的那一周为第 1 周）。问题在于：12 月 31 日可能属于下一年的第 1 周，1 月 1 日也可能属于上一年的第 53 周。

sql
-- 跨年周的经典陷阱
SELECT
  dt,
  EXTRACT(YEAR FROM dt)::INTEGER    AS gregorian_year,
  EXTRACT(ISOYEAR FROM dt)::INTEGER AS iso_year,
  EXTRACT(WEEK FROM dt)::INTEGER    AS iso_week
FROM (VALUES
  (DATE '2026-12-31'),
  (DATE '2027-01-01'),
  (DATE '2027-01-03'),
  (DATE '2027-01-04')
) AS t(dt);
text
     dt     | gregorian_year | iso_year | iso_week
------------+----------------+----------+----------
 2026-12-31 |           2026 |     2026 |       53
 2027-01-01 |           2027 |     2026 |       53
 2027-01-03 |           2027 |     2026 |       53
 2027-01-04 |           2027 |     2027 |        1
(4 rows)

2027-01-01 的公历年是 2027，但 ISO 周纪年仍是 2026——因为它落在 2026 年的第 53 周里。按周分组统计时必须用 isoyear 配对 week，否则会把跨年的同一周拆成两个组：

sql
-- 错误！用 year 配 week，跨年周被拆成两组
SELECT EXTRACT(YEAR FROM paid_at) AS yr, EXTRACT(WEEK FROM paid_at) AS wk, count(*)
FROM orders
GROUP BY yr, wk
ORDER BY yr, wk DESC LIMIT 3;

-- 正确：用 isoyear 配 week
SELECT EXTRACT(ISOYEAR FROM paid_at) AS isoyr, EXTRACT(WEEK FROM paid_at) AS wk, count(*)
FROM orders
GROUP BY isoyr, wk
ORDER BY isoyr, wk DESC LIMIT 3;
text
-- 错误写法的输出（同一 ISO 周被拆开）
  yr  | wk | count
------+----+-------
 2026 | 53 |     2
 2027 | 53 |     1

-- 正确写法的输出（合并为同一周）
 isoyr | wk | count
-------+----+-------
  2026 | 53 |     3

epoch：把时间换算成秒数 ​

epoch 返回自 1970-01-01 00:00:00 UTC 起（对 date 是当日 00:00 UTC）的秒数。它是跨类型比较与差值计算的通用货币：

sql
-- 三种来源的 epoch
SELECT
  EXTRACT(EPOCH FROM TIMESTAMPTZ '2026-09-24 10:30:45.123456+08') AS ts_epoch,
  EXTRACT(EPOCH FROM DATE '2026-09-24')                           AS date_epoch,
  EXTRACT(EPOCH FROM INTERVAL '1 day 2 hours 3 minutes')          AS interval_epoch;
text
      ts_epoch      | date_epoch | interval_epoch
--------------------+------------+----------------
 1790245845.123456  | 1790208000 |          93780

interval 的 epoch 按 1 月 = 30 天（而不是实际月长）换算：

sql
-- 一个日历月的 epoch：按 30 天而非实际天数
SELECT
  EXTRACT(EPOCH FROM INTERVAL '1 mon')  AS one_month_seconds,   -- 2592000 = 30 天
  EXTRACT(EPOCH FROM INTERVAL '30 days') AS thirty_days_seconds; -- 2592000
text
 one_month_seconds | thirty_days_seconds
-------------------+---------------------
           2592000 |             2592000
(1 row)

这意味着 epoch 不适合做精确的月差计算，月差请用 age() 或逐字段相减。

timezone 系列：只在 timestamptz 上有意义 ​

sql
SET TimeZone TO 'UTC';
SELECT EXTRACT(TIMEZONE_HOUR FROM TIMESTAMPTZ '2026-09-24 10:30:00+08') AS tz_h_utc_session;

SET TimeZone TO 'Asia/Shanghai';
SELECT EXTRACT(TIMEZONE_HOUR FROM TIMESTAMPTZ '2026-09-24 10:30:00+08') AS tz_h_sh_session;
text
 tz_h_utc_session
------------------
                0

 tz_h_sh_session
-----------------
               8

注意：EXTRACT(TIMEZONE FROM timestamptz) 返回的是会话时区相对 UTC 的偏移，而不是输入值里写的 +08。因为 timestamptz 存储时已经丢掉了原始偏移信息。这是「时区偏移不可还原」的一个直接体现。

对 timestamp 和 date 抽取 timezone 会返回 0，对 time 抽取也会返回 0（timetz 除外）。

表达式索引：让抽字段的查询也能走索引 ​

直接对列抽字段会让索引失效，但可以反过来建表达式索引：

sql
-- 直接对列抽字段：无法使用 paid_at 上的普通索引
EXPLAIN (COSTS OFF)
SELECT count(*) FROM orders WHERE EXTRACT(MONTH FROM paid_at) = 9;
text
                        QUERY PLAN
-----------------------------------------------------------
 Aggregate
   ->  Seq Scan on orders
         Filter: (EXTRACT(month FROM paid_at) = '9'::numeric)

补一个表达式索引后即可命中：

sql
-- 表达式索引：把抽字段的结果固化到索引里
CREATE INDEX idx_orders_paid_month ON orders (EXTRACT(MONTH FROM paid_at));
ANALYZE orders;

EXPLAIN (COSTS OFF)
SELECT count(*) FROM orders WHERE EXTRACT(MONTH FROM paid_at) = 9;
text
                           QUERY PLAN
-----------------------------------------------------------------
 Aggregate
   ->  Bitmap Heap Scan on orders
         Recheck Cond: (EXTRACT(month FROM paid_at) = '9'::numeric)
         ->  Bitmap Index Scan on idx_orders_paid_month
               Index Cond: (EXTRACT(month FROM paid_at) = '9'::numeric)

注意索引定义里的表达式必须与查询中逐字符一致：索引写 EXTRACT(MONTH FROM paid_at)，查询若写成 date_part('month', paid_at) 就用不上。这也是「同一项目只选一种写法」的另一个理由。

实战示例 ​

步骤 1：按「年-季度」汇总，并验证总行数一致 ​

sql
SELECT
  EXTRACT(YEAR FROM paid_at)::INTEGER    AS yr,
  EXTRACT(QUARTER FROM paid_at)::INTEGER AS qtr,
  count(*)                               AS cnt
FROM orders
GROUP BY yr, qtr
ORDER BY yr, qtr;
text
  yr  | qtr | cnt
------+-----+-----
 2025 |   1 |  83
 2025 |   2 |  91
 2025 |   3 |  92
 2025 |   4 |  91
 2026 |   1 |  90
 2026 |   2 |  91
 2026 |   3 |  74
(7 rows)

步骤 2：用 CASE + isodow 区分工作日与周末 ​

sql
SELECT
  CASE WHEN EXTRACT(ISODOW FROM paid_at) <= 5 THEN '工作日' ELSE '周末' END AS day_type,
  count(*)                                   AS cnt,
  round(sum(amount), 2)                      AS total,
  round(avg(amount), 2)                      AS avg_amount
FROM orders
GROUP BY day_type
ORDER BY day_type;
text
 day_type | cnt |   total   | avg_amount
----------+-----+-----------+------------
 工作日   | 572 | 285736.44 |     499.54
 周末   | 228 | 114392.83 |     501.72
(2 rows)

步骤 3：按小时分布找出「下单高峰时段」 ​

sql
SELECT
  EXTRACT(HOUR FROM paid_at AT TIME ZONE 'Asia/Shanghai')::INTEGER AS hour_of_day,
  count(*) AS cnt
FROM orders
GROUP BY hour_of_day
ORDER BY cnt DESC
LIMIT 5;
text
 hour_of_day | cnt
-------------+-----
          10 |  34
          19 |  34
           4 |  34
           1 |  33
          13 |  34
(5 rows)

这里刻意写了 paid_at AT TIME ZONE 'Asia/Shanghai'，因为timestamptz 抽 hour 时会按会话时区渲染。若生产环境会话时区是 UTC，不写转换就会得到错位 8 小时的结果。

步骤 4：找出「每月 1 号下单」的异常订单 ​

sql
SELECT id, order_no, paid_at
FROM orders
WHERE EXTRACT(DAY FROM paid_at AT TIME ZONE 'Asia/Shanghai') = 1
ORDER BY paid_at
LIMIT 5;
text
 id  |  order_no  |          paid_at
-----+------------+---------------------------
 193 | NO00000193 | 2025-02-01 08:00:00+08
 601 | NO00000601 | 2025-04-01 10:00:00+08
 ...
(5 rows)

步骤 5：用 EXCEPT 验证两种抽取写法结果完全一致 ​

sql
-- 验证 extract 与 date_part 在同一字段上语义一致
WITH a AS (
  SELECT id, EXTRACT(MONTH FROM paid_at)::INTEGER AS m FROM orders
),
b AS (
  SELECT id, date_part('month', paid_at)::INTEGER AS m FROM orders
)
SELECT '仅 extract' AS diff, count(*) FROM (
  SELECT id, m FROM a EXCEPT SELECT id, m FROM b
) x
UNION ALL
SELECT '仅 date_part', count(*) FROM (
  SELECT id, m FROM b EXCEPT SELECT id, m FROM a
) y;
text
     diff     | count
--------------+-------
 仅 extract   |     0
 仅 date_part |     0

双向差集均为空，说明在 ::INTEGER 归一化后两者结果完全一致——差异只体现在中间类型上。

步骤 6：创建表达式索引并量化提升 ​

sql
CREATE INDEX idx_orders_paid_isodow ON orders ((EXTRACT(ISODOW FROM paid_at)));
ANALYZE orders;

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT count(*) FROM orders WHERE EXTRACT(ISODOW FROM paid_at) <= 5;
text
                                QUERY PLAN
---------------------------------------------------------------------------
 Aggregate (actual time=0.412..0.413 rows=1 loops=1)
   ->  Bitmap Heap Scan on orders (actual time=0.105..0.301 rows=572 loops=1)
         Recheck Cond: (EXTRACT(isodow FROM paid_at) <= '5'::numeric)
         Heap Blocks: exact=12
         ->  Bitmap Index Scan on idx_orders_paid_isodow
               Index Cond: (EXTRACT(isodow FROM paid_at) <= '5'::numeric)
         Buffers: shared hit=15
 Execution Time: 0.508 ms

相比 Seq Scan 的每行计算 EXTRACT，表达式索引让代价降为常数级。

注意事项 ​

  1. extract 返回 numeric，date_part 返回 double precision，混用会让 UNION 结果类型不确定：
    sql
    -- 不推荐：两个分支类型不同，union 会提升为 double precision
SELECT EXTRACT(YEAR FROM paid_at) FROM orders
UNION
SELECT date_part('year', paid_at) FROM orders;
  2. date_part 的第一个参数是字符串，必须加引号。date_part(year, paid_at) 会被解析成列引用而报错。
  3. dow 里周日是 0 不是 7。用 dow BETWEEN 1 AND 5 判工作日恰好正确（0 是周日被排除），但可读性差且极易与 isodow 混淆——统一用 isodow 更安全。
  4. week 是 ISO 周号，必须配 isoyear，配 year 会在跨年周产生错误分组：
    sql
    -- 错误！跨年周被拆分
GROUP BY EXTRACT(YEAR FROM ts), EXTRACT(WEEK FROM ts)
-- 正确
GROUP BY EXTRACT(ISOYEAR FROM ts), EXTRACT(WEEK FROM ts)
  5. EXTRACT(SECOND ...) 返回带小数，EXTRACT(MINUTE ...) 返回整数，做算术时要留意类型：
    sql
    SELECT EXTRACT(SECOND FROM now()) * 2;      -- 结果是带小数的 numeric
  6. epoch 对 interval 按 1 月 = 30 天换算，不能用于精确月差；月差用 age()。
  7. EXTRACT(TIMEZONE FROM timestamptz) 返回会话时区偏移，不是输入值里的偏移。要保留原始偏移必须额外存一列。
  8. 列上套 EXTRACT 会让普通索引失效，但可以建表达式索引；索引表达式必须与查询逐字符一致（函数名、大小写、空格）。
  9. EXTRACT 在 WHERE 中不可 sargable，优先改写成范围条件：
    sql
    -- 不推荐
WHERE EXTRACT(MONTH FROM paid_at) = 9
-- 推荐（可走 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'
  10. 不要把 EXTRACT 用在分区键表达式上再期待分区裁剪，timestamptz 上的 EXTRACT 不是 IMMUTABLE，无法参与分区裁剪。
  11. isodow 与 to_char(ts, 'ID') 编号一致，但 dow 与 to_char(ts, 'D') 不一致。跨函数混用时务必核对编号表。
  12. EXTRACT 对 date 类型抽 hour 恒返回 0，不会报错，容易掩盖「误把 date 当 timestamp 用」的问题。

最佳实践 ​

  1. 全项目统一使用 EXTRACT(field FROM src)，它是 SQL 标准，可读性更好，且返回 numeric 不会有浮点表示误差。
  2. 需要整数就显式 ::INTEGER，不要依赖隐式转换；::INTEGER 对 numeric 与 double 都是四舍五入。
  3. 判定工作日一律用 isodow：isodow BETWEEN 1 AND 5；判定周末用 isodow IN (6, 7)。
  4. 按周分组一律用 isoyear + week 配对，并在报表上写明是 ISO 周而非公历周。
  5. 对 timestamptz 抽时段字段前先 AT TIME ZONE 定锚业务时区：
    sql
    EXTRACT(HOUR FROM paid_at AT TIME ZONE 'Asia/Shanghai')
  6. 高频的抽字段条件优先改写成范围条件，只有确实无法改写时才建表达式索引。
  7. 建表达式索引时把索引定义中的表达式原样复制到查询里，不要凭记忆重写。
  8. 用 epoch 做时间差：EXTRACT(EPOCH FROM (t2 - t1)) 得到秒数，比手工拆分字段更简洁：
    sql
    SELECT EXTRACT(EPOCH FROM (now() - created_at))::INTEGER AS age_seconds FROM orders;
  9. 不要用 epoch 做跨月的精确差值，用 age() 或对 day 字段相减。
  10. 报表里同时展示「原值」和「抽出的字段」，便于人工核对，例如 paid_at, EXTRACT(HOUR FROM paid_at AT TIME ZONE 'Asia/Shanghai') AS h。
  11. EXTRACT 与 date_trunc 配合使用：先截断到精度边界再抽字段，可以避免「同一个月不同天」被拆散。
  12. 写测试覆盖跨年周、闰年 2 月 29 日、月末 31 日这三个边界，它们最容易暴露 EXTRACT 用错字段的问题。

下一节

继续学习：DATE_TRUNC 截断

参考链接 ​