Laravel中使用selectRaw按created_at月份分组如何返回有效日期

问题原因 #

你使用MONTH()函数仅会提取日期中的月份数值，所以只会返回1-12的数字，无法得到完整的有效日期格式，同时如果统计范围跨多个年份，还会出现不同年份的同月份数据被合并统计的问题。

按月份分组的正确修改代码（MySQL环境） #

User::selectRaw('DATE_FORMAT(created_at, "%Y-%m-01") as date, COUNT(*) as total')
    ->groupBy('date')
    ->where('created_at', '>=', \Carbon\Carbon::now()->subWeeks(4))
    ->whereNull('email_verified_at')
    ->get();

这里用DATE_FORMAT函数把日期格式化为年-月-01的格式，返回的date字段是类似2024-10-01的标准日期格式，同时可以区分不同年份的同月份数据。

其他时间粒度的实现参考 #

• 按年分组

->selectRaw('DATE_FORMAT(created_at, "%Y-01-01") as date, COUNT(*) as total')

返回年-01-01格式的日期，示例：2024-01-01

• 按小时分组

->selectRaw('DATE_FORMAT(created_at, "%Y-%m-%d %H:00:00") as date, COUNT(*) as total')

返回年-月-日 时:00:00格式的日期，示例：2024-10-09 14:00:00

• 按周分组（周一为每周第一天）

->selectRaw('DATE_SUB(DATE(created_at), INTERVAL WEEKDAY(created_at) DAY) as date, COUNT(*) as total')

返回每周周一的日期作为分组标识。

如果你使用PostgreSQL环境，把DATE_FORMAT替换为to_char即可，比如按月分组的写法：

->selectRaw('to_char(created_at, \'YYYY-MM-01\') as date, COUNT(*) as total')

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