DATE_TRUNC是最直接的自然周期分组方式,需用单数小写粒度(如'day');SQLite/MySQL用strftime/DATE_FORMAT转字符串;非自然周期需偏移对齐;必须GROUP BY时间表达式,注意时区。

用 DATE_TRUNC 按自然周期分组(PostgreSQL / BigQuery)
PostgreSQL 和 BigQuery 原生支持 DATE_TRUNC,是最直接的方式。它能把时间戳截断到指定精度(如 'day'、'week'、'month'),天然对齐日历边界。
常见错误是传错粒度字符串:写成 'days' 或 'monthly' 会报错,必须用单数小写形式('day'、'month')。
示例:按周统计订单量,从周一算起(PostgreSQL 默认行为):
SELECT DATE_TRUNC('week', order_time) AS week_start,
COUNT(*) AS order_count
FROM orders
GROUP BY DATE_TRUNC('week', order_time)
ORDER BY week_start;注意:DATE_TRUNC('week', ...) 在不同数据库中起始日可能不同(PostgreSQL 从周一,BigQuery 从周日),需结合 EXTRACT(DOW FROM ...) 验证或用 DATE_SUB 调整。
用 strftime 或 DATE_FORMAT 构造自定义周期字符串(SQLite / MySQL)
SQLite 用 strftime,MySQL 用 DATE_FORMAT,本质都是把时间转成可分组的字符串。适合需要灵活控制格式的场景,比如“2024-W23”这种 ISO 周格式,或“2024Q2”季度标识。
容易踩的坑:
- SQLite 的
strftime('%Y-%m', ts)返回的是字符串,不能直接参与日期运算;若后续要排序,得确保格式固定(如'%Y-%m'比'%Y-%m-%d'更安全) - MySQL 中
DATE_FORMAT(NOW(), '%Y%u')的%u是周号(1–53),但第1周定义依赖default_week_format,跨年时可能错位
示例:MySQL 中按“年+季度”分组:
SELECT DATE_FORMAT(order_time, '%Y') + FLOOR((MONTH(order_time)-1)/3) AS year_quarter,
COUNT(*)
FROM orders
GROUP BY year_quarter;更稳妥写法是用 CONCAT(YEAR(order_time), 'Q', QUARTER(order_time)),避免数值计算溢出。
手动计算偏移实现任意起始点的周期(如每7天从周三开始)
当业务要求不是日历自然周期(例如“每周三至下周二为一个周期”),就得靠时间偏移。核心思路:把原始时间减去固定偏移量,再截断,最后加回偏移。
关键步骤:
- 确定基准偏移:比如想让周期从周三 00:00 开始,则偏移 = 当前时间到最近一个周三 00:00 的差值
- 用
order_time - INTERVAL '2 days'(PostgreSQL)或DATE_SUB(order_time, INTERVAL 2 DAY)(MySQL)先对齐 - 再对齐后的时间做
DATE_TRUNC('week')或DATE_FORMAT(..., '%Y-%m-%d')
示例(PostgreSQL):每7天一组,起始日为周三 00:00:
SELECT DATE_TRUNC('week', order_time - INTERVAL '2 days') + INTERVAL '2 days' AS cycle_start,
COUNT(*)
FROM orders
GROUP BY DATE_TRUNC('week', order_time - INTERVAL '2 days') + INTERVAL '2 days';这个表达式在高并发或大数据量下性能尚可,但务必给 order_time 加索引——否则每次都要全表计算偏移。
窗口函数不适用,聚合必须用 GROUP BY 时间表达式
有人尝试用 ROW_NUMBER() OVER (ORDER BY order_time) 再除以 N 取整来模拟分组,这是错的。窗口函数无法改变行数,也不能替代 GROUP BY 的语义。
典型错误现象:
- 结果行数没减少,只是多了一列序号
- 聚合值(如
SUM(amount))没按周期汇总,而是每行重复相同值
真正要聚合,必须把时间表达式放进 GROUP BY。哪怕表达式很长(比如带多个 EXTRACT 和运算),也得原样复现一次——MySQL 8.0+ 允许用别名,但 PostgreSQL 和旧版 MySQL 仍需写两遍。
最易被忽略的一点:时间字段是否带有时区?order_time 是 TIMESTAMP WITHOUT TIME ZONE 还是 TIMESTAMP WITH TIME ZONE,会导致 DATE_TRUNC 结果差出一整天。上线前一定要查表定义和数据样例。

















