如何在SQL中按日期、月份、周分组统计？含timestamp字段报错解决

报错原因 #

你的查询中SELECT子句返回的是原始timestamp类型的createdAt，但GROUP BY使用的是转换后的日期类型表达式，两者不匹配，导致数据库无法匹配对应的聚合逻辑，因此抛出函数/类型不匹配的错误。

1. 按日期（YYYY-MM-DD）统计记录数 #

将SELECT中的createdAt替换为和GROUP BY一致的日期转换表达式，同时调整日期范围确保包含目标时间段的全天数据：

SELECT date("createdAt") AS period, COUNT(id) AS count
FROM members
WHERE "createdAt" >= '2022-08-01' AND "createdAt" < '2022-08-11'
GROUP BY date("createdAt");

注：用>=和<替代BETWEEN可以避免漏掉2022-08-10当天非零点的记录，BETWEEN '2022-08-01' AND '2022-08-10'仅包含到2022-08-10 00:00:00的记录。

2. 按星期名称（如Monday）统计记录数 #

使用to_char函数将timestamp转换为星期名称，同时保证SELECT和GROUP BY的表达式一致：

SELECT to_char("createdAt", 'FMDay') AS period, COUNT(id) AS count
FROM members
WHERE "createdAt" >= '2022-08-01' AND "createdAt" < '2022-08-11'
GROUP BY to_char("createdAt", 'FMDay');

如果需要按星期顺序排序（周一到周日），可以加上排序逻辑：

SELECT to_char("createdAt", 'FMDay') AS period, COUNT(id) AS count
FROM members
WHERE "createdAt" >= '2022-08-01' AND "createdAt" < '2022-08-11'
GROUP BY to_char("createdAt", 'FMDay'), extract(dow from "createdAt")
ORDER BY extract(dow from "createdAt");

注：FMDay用于去除星期名称前后的空格，extract(dow from "createdAt")返回星期对应的数字（0为周日，1为周一，以此类推），用于排序。

内容的提问来源于stack exchange，提问作者FaFa