SQL中YEAR和MONTH函数如何按月份统计数据？-SQL-PHP中文网

year和month函数返回整数而非日期类型，如year('2023-04-15')得2023、month得4；直接用于分组或拼接易致排序错乱，正确做法是用date_format('%y-%m')或year*100+month生成可排序键。

sql中year和month函数如何按月份统计数据？

YEAR和MONTH函数返回的是整数，不是日期类型

很多人以为 YEAR() 和 MONTH() 返回的是“年份字符串”或“带格式的月份”，其实它们只返回纯整数：比如 YEAR('2023-04-15') 得到 2023，MONTH('2023-04-15') 得到 4。这意味着你不能直接拿它去排序或分组“自然月”，比如 2023-1 和 2023-10 在字符串排序里会错乱，但整数本身不带前导零，也没法拼成标准日期格式。

常见错误现象：
- 按 YEAR(create_time), MONTH(create_time) 分组后，发现 2023 年 10 月排在 2023 年 1 月前面（因为 10 > 1，数值上没问题，但人眼读起来不直观）；
- 想用 CONCAT(YEAR(d), '-', MONTH(d)) 得到类似 '2023-4'，结果 4 月变成 '2023-4'，10 月是 '2023-10'，排序依然错位；
- 把 YEAR() 和 MONTH() 单独作为维度展示，却漏掉跨年场景下月份无法横向对比（比如想看各年 12 月 vs 1 月的增长）。

正确做法是用 DATE_FORMAT(create_time, '%Y-%m')（MySQL）或 TO_CHAR(create_time, 'YYYY-MM')（PostgreSQL）生成统一格式的年月字符串
如果必须用整数做分组（比如后续要计算同比），推荐组合成一个可排序的整数键：YEAR(create_time) * 100 + MONTH(create_time) → 得到 202304、202310，天然支持数值排序
注意：SQL Server 没有 DATE_FORMAT，得用 FORMAT(create_time, 'yyyy-MM') 或 CONVERT(VARCHAR(7), create_time, 120)

按月份统计时，GROUP BY 必须覆盖时间粒度全维度

只写 GROUP BY MONTH(create_time) 会把所有年份的 1 月全合在一起，比如 2021 年 1 月、2022 年 1 月、2023 年 1 月的数据全加总成一条记录 —— 这通常不是你想要的“按月份统计”，而是“按月份数字统计”。

使用场景：
- 要看每月新增用户数 → 需要区分年份，否则数据无意义；
- 要看“每年 12 月销售额对比” → 先按年分组，再提取 12 月数据，而不是全局按 MONTH() 分组；
- 做折线图的时间轴 → X 轴必须是有序的“年-月”，不能只是 1~12 的循环数字。

安全写法：始终同时包含年与月信息，例如 GROUP BY YEAR(create_time), MONTH(create_time)，再用 ORDER BY YEAR(create_time), MONTH(create_time)
更推荐：用 GROUP BY DATE_FORMAT(create_time, '%Y-%m')，既语义清晰，又避免跨年混淆
别漏掉 WHERE 条件过滤无效时间（如 create_time IS NOT NULL），否则 YEAR(NULL) 返回 NULL，导致统计中混入一条“未知年份”记录

性能影响：函数包裹字段会让索引失效

写 WHERE YEAR(create_time) = 2023 或 GROUP BY DATE_FORMAT(create_time, '%Y-%m')，数据库无法使用 create_time 字段上的普通 B-tree 索引，因为函数改变了原始值的可比较结构。尤其在千万级表上，可能从毫秒变几秒。

参数差异：
- YEAR(create_time) = 2023：全表扫描年份计算
- create_time >= '2023-01-01' AND create_time ：能走索引，快得多

替代方案：用范围查询代替函数提取，例如统计 2023 年每月数据，先用 WHERE 限定年份范围，再在子查询或 CTE 中按月分组
如果高频按年月查，考虑建生成列（MySQL 5.7+）：ALTER TABLE orders ADD COLUMN ym CHAR(7) GENERATED ALWAYS AS (DATE_FORMAT(create_time, '%Y-%m')) STORED，然后给 ym 加索引
PostgreSQL 可建函数索引：CREATE INDEX idx_orders_ym ON orders ((TO_CHAR(create_time, 'YYYY-MM')))

不同数据库对月份起始日和时区处理不一致

MONTH() 本身不涉及时区，但输入时间字段若为 TIMESTAMP 类型，在 MySQL 中默认按系统时区解释；PostgreSQL 的 TIMESTAMP WITH TIME ZONE 会自动转成本地时区再取月，而 TIMESTAMP WITHOUT TIME ZONE 则原样解析 —— 同一条记录，在不同时区设置下，MONTH(created_at) 可能返回不同值。

线上库务必确认 create_time 字段类型：优先用 TIMESTAMP（带时区语义）而非 DATETIME（MySQL）或裸 TIMESTAMP（PostgreSQL）
应用写入时统一用 UTC 时间存入，查询时再按需转换，避免因服务器时区配置变更导致统计数据漂移
测试时用明确带时区的字面量验证，例如 MySQL：SELECT MONTH('2023-01-01 00:00:00+08:00')，PostgreSQL：SELECT EXTRACT(MONTH FROM TIMESTAMP '2023-01-01 00:00:00+08')

实际写 SQL 时，最易被忽略的是：你以为按 MONTH() 分组就能得到“自然月序列”，但没意识到它不带年份上下文就毫无业务意义；更麻烦的是，一旦用了函数包装时间字段，又忘了索引失效这回事，查着查着就慢了。