Laravel结合PostgreSQL时年月格式日期排序错误问题

数据库结构 #

问题描述 #

需要基于不同状态值，绘制指定时间周期内的总成本统计图表，查询需返回对应年月维度及该时段的总成本数值。实际查询时即使添加了order by子句设置排序规则，返回的图表数据仍然处于乱序状态。
初始查询代码：

$delivery_failure = DB::table('partners_sms')->select(DB::raw('month, sum(sum) as y'))
                ->whereBetween('created_at', [new Carbon($start_date), new Carbon($end_date)])
                ->where('failure_reason', '=', 'DeliveryFailure')
                ->groupBy('month')
                ->orderBy('month', 'DESC')
                ->get();

尝试过的调整方案（仍未解决问题）：

$successNonPartner = SMSData::query()->selectRaw("to_char(created_at::timestamp, 'MONTH-YYYY') as month")
            ->whereBetween('created_at', [new Carbon($start_date), new Carbon($end_date)])
            ->where('status', '=', 'Success')
            ->groupBy('created_at')
            ->orderBy('created_at', 'DESC')
            ->get();

运行环境：Laravel框架 + PostgreSQL数据库，需要实现日期格式化为年月格式、且日期按时间先后正确排序的查询方案。

问题原因 #

• 第一个查询直接使用表中存储的month字段分组排序，如果该字段是字符串格式（如JANUARY-2024这类月份名在前的格式），字符串排序默认按字符ASCII码排序，和实际时间顺序不匹配，就会出现乱序。
• 第二个查询存在两个问题：一是group by使用了原始created_at字段，该字段是精确到秒级的时间戳，相当于按单条记录分组，完全达不到按月聚合统计的效果；二是如果直接按格式化后的月份字符串排序，同样会触发字符串排序的乱序问题。

实现方案 #

核心逻辑是用PostgreSQL内置的date_trunc函数先把created_at时间戳截断到月份维度，用这个时间类型的截断值做分组和排序——时间类型的排序天然遵循时间先后顺序，不会出现乱序，最后再把截断后的时间格式化为需要的年月字符串输出即可。

推荐写法（逻辑直观，性能好）

// 配送失败维度统计
$delivery_failure = DB::table('partners_sms')
    ->select(DB::raw("
        to_char(date_trunc('month', created_at), 'MONTH-YYYY') as month,
        sum(sum) as y,
        date_trunc('month', created_at) as sort_month
    "))
    ->whereBetween('created_at', [new Carbon($start_date), new Carbon($end_date)])
    ->where('failure_reason', '=', 'DeliveryFailure')
    ->groupBy('sort_month')
    ->orderBy('sort_month', 'DESC')
    ->get()
    ->makeHidden(['sort_month']); // 隐藏仅用于排序的临时字段，不返回给前端

// 发送成功（非合作方）维度统计
$successNonPartner = SMSData::query()
    ->selectRaw("
        to_char(date_trunc('month', created_at), 'MONTH-YYYY') as month,
        sum(cost) as y, // 替换为实际要统计的成本字段名
        date_trunc('month', created_at) as sort_month
    ")
    ->whereBetween('created_at', [new Carbon($start_date), new Carbon($end_date)])
    ->where('status', '=', 'Success')
    ->groupBy('sort_month')
    ->orderBy('sort_month', 'DESC')
    ->get()
    ->makeHidden(['sort_month']);

如果需要输出数字格式的年月（如01-2024），只需把to_char的格式参数改为'MM-YYYY'即可，排序逻辑不受影响。

可选写法（不返回多余排序字段）

如果不想在结果集中携带多余的排序字段，可以用子查询先完成分组排序，再格式化输出，性能和上面的写法完全一致：

$delivery_failure = DB::table('partners_sms')
    ->select(DB::raw("
        to_char(sort_month, 'MONTH-YYYY') as month,
        sum(sum) as y
    "))
    ->fromSub(function ($query) use ($start_date, $end_date) {
        $query->from('partners_sms')
            ->selectRaw("date_trunc('month', created_at) as sort_month, sum")
            ->whereBetween('created_at', [new Carbon($start_date), new Carbon($end_date)])
            ->where('failure_reason', '=', 'DeliveryFailure');
    }, 'tmp')
    ->groupBy('sort_month')
    ->orderBy('sort_month', 'DESC')
    ->get();

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