EXTRACT 是什么
EXTRACT(unit FROM expr) 从日期时间值里取出单个字段：年、月、日、时、分、秒、周、季度。这是 SQL 标准写法，MySQL 8 与 PostgreSQL 都支持；MySQL 另有 YEAR()、MONTH() 这类简写函数，PostgreSQL 则扩展了 DOW、ISODOW、EPOCH 等单位。
基本信息
| 通用语法 | EXTRACT(YEAR FROM created_at) |
|---|---|
| 常用单位 | YEAR / MONTH / DAY / HOUR / MINUTE / SECOND / QUARTER / WEEK |
| MySQL 简写 | YEAR()、MONTH()、DAY()、HOUR()、MINUTE()、SECOND()、QUARTER() |
| PostgreSQL 扩展 | DOW（周日为 0）、ISODOW（周一为 1）、DOY、EPOCH |
| 返回类型 | PostgreSQL 返回 numeric；MySQL 返回整数 |
| 索引影响 | 对列做 EXTRACT 之后再过滤，普通索引无法使用 |
拆出年月日时
拆出年月日时
SELECT EXTRACT(YEAR FROM o.created_at) AS y,
EXTRACT(MONTH FROM o.created_at) AS m,
EXTRACT(DAY FROM o.created_at) AS d,
EXTRACT(HOUR FROM o.created_at) AS h
FROM orders o
WHERE o.created_at >= '2024-06-01' -- 先按列过滤，保住索引
AND o.created_at < '2024-07-01'
ORDER BY y, m, d, h; -- 取字段是为了分组展示，不是为了过滤简写与扩展单位
简写与扩展单位
-- MySQL 简写，可读性更好
SELECT YEAR(created_at), MONTH(created_at), DAYOFMONTH(created_at) FROM orders;
-- PostgreSQL 扩展单位：季度、ISO 周内日、自 1970 起的秒数
SELECT EXTRACT(QUARTER FROM ts) AS q,
EXTRACT(ISODOW FROM ts) AS iso_dow,
EXTRACT(EPOCH FROM ts) AS epoch_secs
FROM t; -- 注意 EPOCH 返回 numeric，序列化前按需 CAST常用单位对照
| 单位 | 含义 | 说明 |
|---|---|---|
| YEAR | 四位年份 | 两库行为一致 |
| MONTH | 月份 1 到 12 | 两库行为一致 |
| QUARTER | 季度 1 到 4 | MySQL 与 PG 都支持 |
| WEEK | 周序号 | 口径随参数变化，跨系统前先对齐 |
| DOW / ISODOW | 周内日 | PG 单位；MySQL 用 DAYOFWEEK、WEEKDAY |
| EPOCH | 自 1970 起的秒数 | PG 返回 numeric |
典型使用场景
- 按年月出趋势：月度汇总、同比环比的基础
- 按小时看流量分布，定位业务高峰
- 按季度汇总，匹配财报与考核口径
- 按周内日分析，比较周末与工作日的差异
- 时间戳与日期互转前先拆解字段，便于分组展示
- 排查脏数据：看是否存在年份异常的记录
- 把年、月拆成两列导出，方便下游直接做透视
- 跨年周统计：先确定按自然年还是按 ISO 周归属年份
WEEK 的编号口径最容易出错：PostgreSQL 的 EXTRACT(WEEK) 是 ISO 周（周一为一周开始，第 1 周含 1 月 4 日）；MySQL 的 WEEK() 结果受 default_week_format 影响。跨系统对比周数据前先确认口径。
按周统计前先定义清楚业务口径：自然周（周一到周日）还是 ISO 周，跨年那一周归到哪一年。用 EXTRACT(WEEK) 与用年加周序号组合的结果不同，口径要写进注释。
字段本身带时区时，先 AT TIME ZONE 换算到业务时区再取字段，否则东八区凌晨的数据会被归到前一天，日报与流水对不上。
WHERE EXTRACT(YEAR FROM created_at) = 2024 会让索引完全用不上，必须改写成范围条件：created_at >= '2024-01-01' AND created_at < '2025-01-01'。