我想得到给定月份的ot小时总数,可能超过1000小时。我已经有时间总和查询,但它限制了839:59:59。如何忽略或其他方法来完成此任务。(ot_hours存储在VARCHAR
数据类型中(
尝试查询
SELECT SEC_TO_TIME( SUM( TIME_TO_SEC( `ot_hour` ) ) ) AS timeSum
FROM groupotrequest
INNER JOIN department
ON department.dept_Id = groupotrequest.dept_Id
WHERE groupotrequest.dept_Id = $dept_Id AND month LIKE '$passmonth%' AND overtime_status=6
样本数据
id month ot_hour dept_Id overtime_status overtime_type date
1 2019-10 2535:00:00 28 6 main_ot 2019-11-21 07:26:00
2 2019-11 2535:00:00 28 6 main_ot 2019-11-21 07:27:00
3 2019-10 20:00 28 6 sub_ot 2019-11-21 07:28:00
4 2019-11 20:00 28 6 sub_ot 2019-11-21 07:30:00
我想获得2019-11年的总营业时间为2555:00:00
function GetApprovedOt($connect,$dept_Id,$passmonth)
{
$query01 =$connect->prepare('SELECT SUM( TIME_TO_SEC( `ot_hour` ))
FROM groupotrequest
INNER JOIN department
ON department.dept_Id = groupotrequest.dept_Id
WHERE groupotrequest.dept_Id = :dept_Id AND month LIKE :passmonth% AND overtime_status=6');
$query01->execute([
':passmonth' => $passmonth,
':dept_Id' => $dept_Id
]);
list ($totalMins, $remngSecs) = gmp_div_qr($query01->fetchColumn(), 60);
list ($totalHour, $remngMins) = gmp_div_qr($totalMins, 60);
echo "Worked a total of $totalHour:$remngMins:$remngSecs.";
}
我也试过这个。它也不工作
假设数据中有2535:00:00
等值,则不能使用time
数据类型或time_to_sec
等函数。
一种解决方法是将字符串2535:30:00
转换为分钟(2535*60+30=152130(,执行求和,然后将求和转换回小时和分钟(152130=2535小时,30分钟(:
SELECT SUM(
SUBSTRING_INDEX(ot_hour, ':', 1) * 60 +
SUBSTRING_INDEX(SUBSTRING_INDEX(ot_hour, ':', 2), ':', -1)
) div 60 AS sum_hh,
SUM(
SUBSTRING_INDEX(ot_hour, ':', 1) * 60 +
SUBSTRING_INDEX(SUBSTRING_INDEX(ot_hour, ':', 2), ':', -1)
) mod 60 AS sum_mm
FROM t