Laravel中如何计算所有起止时间总和的差值？

需求：对查询获取的包含start_time和end_time的数据集，分别累加所有条目的时间总和，再计算两者的差值。

示例数据集：

[
  {
    "id": 108172,
    "qc_id": 1319,
    "audit_id": 104132,
    "start_time": "11:1:49",
    "end_time": "11:1:52"
  },
  {
    "id": 108191,
    "qc_id": 1319,
    "audit_id": 104132,
    "start_time": "11:2:3",
    "end_time": "11:13:26"
  },
  {
    "id": 108192,
    "qc_id": 1319,
    "audit_id": 104132,
    "start_time": "11:13:30",
    "end_time": "11:13:45"
  }
]

原代码问题分析 #

原代码的核心错误是将Laravel查询返回的Collection对象直接传入json_decode，get()方法返回的是Eloquent集合，并非JSON字符串，这会导致后续遍历逻辑失效。

修正后的实现方式 #

方式一：优化循环逻辑

$qcAuditTimeTaken = QcAuditTimeTaken::where('audit_id', base64_decode($store_id))->get();

$startTimeSum = 0;
$endTimeSum = 0;

foreach ($qcAuditTimeTaken as $entry) {
    // 解析start_time并转换为秒数累加
    [$hours, $minutes, $seconds] = explode(':', $entry->start_time);
    $startTimeSum += $hours * 3600 + $minutes * 60 + $seconds;
    
    // 解析end_time并转换为秒数累加
    [$hours, $minutes, $seconds] = explode(':', $entry->end_time);
    $endTimeSum += $hours * 3600 + $minutes * 60 + $seconds;
}

// 计算总差值
$timeDifference = $endTimeSum - $startTimeSum;

// 转换回时分秒格式
$hours = floor($timeDifference / 3600);
$minutes = floor(($timeDifference % 3600) / 60);
$seconds = $timeDifference % 60;

echo "时间差值：{$hours}小时，{$minutes}分钟，{$seconds}秒";

方式二：利用Laravel集合方法简化代码

$qcAuditTimeTaken = QcAuditTimeTaken::where('audit_id', base64_decode($store_id))->get();

// 定义时间转秒数的辅助函数
$timeToSeconds = function ($time) {
    [$hours, $minutes, $seconds] = explode(':', $time);
    return $hours * 3600 + $minutes * 60 + $seconds;
};

// 用集合sum方法直接计算总和
$startTimeSum = $qcAuditTimeTaken->sum(fn($item) => $timeToSeconds($item->start_time));
$endTimeSum = $qcAuditTimeTaken->sum(fn($item) => $timeToSeconds($item->end_time));

$timeDifference = $endTimeSum - $startTimeSum;

// 格式化输出
$hours = floor($timeDifference / 3600);
$minutes = floor(($timeDifference % 3600) / 60);
$seconds = $timeDifference % 60;

echo "时间差值：{$hours}小时，{$minutes}分钟，{$seconds}秒";

方式三：数据库层面计算（适合大数据量）

如果数据集较大，推荐直接在数据库中完成求和计算，减少PHP的处理压力（以MySQL为例）：

$result = QcAuditTimeTaken::where('audit_id', base64_decode($store_id))
    ->selectRaw('
        SUM(TIME_TO_SEC(start_time)) as start_total,
        SUM(TIME_TO_SEC(end_time)) as end_total
    ')
    ->first();

$timeDifference = $result->end_total - $result->start_total;

// 格式化输出同上
$hours = floor($timeDifference / 3600);
$minutes = floor(($timeDifference % 3600) / 60);
$seconds = $timeDifference % 60;

echo "时间差值：{$hours}小时，{$minutes}分钟，{$seconds}秒";

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