时间截断是什么
时间截断把时间戳归到指定粒度的起点，是时间分桶统计的核心工具。PostgreSQL 原生提供 date_trunc('month', ts)，支持 day、week、month、quarter、year 等粒度。MySQL 没有同名函数，通常组合实现：DATE(created_at) 截到天，DATE_FORMAT(created_at, '%Y-%m-01') 截到月首，按小时则格式化成整点。截断结果仍是时间类型（MySQL 的部分写法返回字符串），参与排序与范围比较更自然，这是它比直接格式化更实用的地方。
基本信息
| PostgreSQL | date_trunc('day'|'week'|'month'|'quarter'|'year', ts) |
|---|---|
| 星期起点 | PostgreSQL 的 week 以周一为起点 |
| MySQL 截到天 | DATE(created_at) |
| MySQL 截到月首 | DATE_FORMAT(created_at, '%Y-%m-01')，返回字符串 |
| MySQL 截到整点 | DATE_FORMAT(created_at, '%Y-%m-%d %H:00:00') |
| 平台差异 | MySQL 无 date_trunc，需要组合函数实现 |
典型使用场景
- 按天或按月出报表：把时间戳归到天或月再分组
- 按小时看流量峰谷：整点分桶统计请求量
- 按周统计：对齐周期做周环比
- 同比环比：截断到月后与去年同期对比
- 留存分析：把注册日与活跃日都截断到天再计算间隔
- 数据对账：按天对齐两张表的统计口径
示例
示例
-- PostgreSQL
SELECT date_trunc('day', created_at) AS d, COUNT(*) FROM orders GROUP BY 1 ORDER BY 1;
SELECT date_trunc('month', created_at) AS m, SUM(amount) FROM orders GROUP BY 1 ORDER BY 1;
-- MySQL：截到天
SELECT DATE(created_at) AS d, COUNT(*) FROM orders GROUP BY DATE(created_at) ORDER BY d;
-- MySQL：截到月首，再按字符串分组
SELECT DATE_FORMAT(created_at, '%Y-%m-01') AS m, SUM(amount)
FROM orders GROUP BY DATE_FORMAT(created_at, '%Y-%m-01') ORDER BY m;粒度与写法对照
| 粒度 | PostgreSQL | MySQL |
|---|---|---|
| 天 | date_trunc('day', ts) | DATE(ts) |
| 小时 | date_trunc('hour', ts) | DATE_FORMAT(ts, '%Y-%m-%d %H:00:00') |
| 月 | date_trunc('month', ts) | DATE_FORMAT(ts, '%Y-%m-01') |
| 季度 | date_trunc('quarter', ts) | 需按月份计算后拼季度首日 |
| 年 | date_trunc('year', ts) | DATE_FORMAT(ts, '%Y-01-01') |
截断后的分组键通常无法使用原时间列索引，能配合 WHERE 限定范围就先限定，减少参与分组的数据量。
MySQL 里 DATE_FORMAT 返回字符串，按它排序是字符串顺序；格式统一成零填充的写法（如 2024-05-01）才与时间顺序一致。
缺失的日期不会自动补零：按天统计时没有数据的日期不会出现，需要补零要左连接一张日期维表。
使用要点
- 先 WHERE 限定时间范围，再做截断分组
- 月与周的分组键建议格式化成零填充形式，保证排序正确
- 报表缺日期时用日期维表左连接补零
- 截断粒度与业务口径对齐：自然周还是周一为起点要写清
- 跨库迁移时把 date_trunc 逐个改写成 MySQL 的等价组合
- 把常用粒度封装成视图或函数，减少重复写法的不一致