基于07:24:00:0000阈值计算总加班时长的SQL查询问题排查

首先，咱们把你当前代码里的问题一一拆解出来，再给出可行的修正方案：

1. 数据库字段拼写错误 #

你的SQL里写的是month(starttid)，但数据库里的字段名是starttime——这是个低级但致命的拼写错误，会直接导致SQL找不到对应字段而报错。

2. 统计函数用错了 #

你用COUNT()来计算加班总和，但COUNT()是用来统计符合条件的记录数量的，不是累加数值。要计算总加班时长，必须用SUM()函数。

3. 加班时长计算逻辑错误 #

• 你当前的逻辑是直接对所有记录的(工作时长 - 阈值)做统计，但如果某天工作时长不足7小时24分，差值会是负数，这部分不应该被计入总加班（总加班不能为负）。
• SUBTIME的参数格式有误：'07:24:00:0000'多了四位毫秒，正确的时间间隔格式应该是'07:24:00'。

4. 时间差计算的精度问题 #

用TIMESTAMPDIFF(HOUR, starttime, endtime)只能得到整数小时数，会丢失分钟级精度（比如7小时30分只会被算成7小时），导致加班时长计算不准确。

修正后的代码实现 #

下面是调整后的函数，我把逻辑改成：计算每条记录的实际工作时长（精确到秒），减去阈值（7小时24分=26640秒），仅累加差值大于0的部分，同时避免跨年月统计的问题：

public function getOvertimeTotal($id, $selectedMonth, $selectedYear = null) {
    // 预设年份，若调用方未传入则用当前年份
    if ($selectedYear === null) {
        $selectedYear = date('Y');
    }
    // 计算阈值对应的秒数：7小时24分 = 7*3600 + 24*60 = 26640秒
    $thresholdSeconds = 7 * 3600 + 24 * 60;
    
    $sql = "
        SELECT 
            SUM(
                GREATEST(
                    TIMESTAMPDIFF(SECOND, starttime, endtime) - :threshold,
                    0
                )
            ) as overTimeTotalSeconds
        FROM hours 
        WHERE user_id = :id 
        AND MONTH(starttime) = :selectedMonth
        AND YEAR(starttime) = :selectedYear
    ";
    
    $this->db->query($sql);
    $this->db->bind(':id', $id);
    $this->db->bind(':selectedMonth', $selectedMonth);
    $this->db->bind(':selectedYear', $selectedYear);
    $this->db->bind(':threshold', $thresholdSeconds);
    
    $row = $this->db->single();
    
    // 将总秒数转换为时分秒格式返回
    if (!empty($row->overTimeTotalSeconds)) {
        $hours = floor($row->overTimeTotalSeconds / 3600);
        $minutes = floor(($row->overTimeTotalSeconds % 3600) / 60);
        $seconds = $row->overTimeTotalSeconds % 60;
        return sprintf('%02d:%02d:%02d', $hours, $minutes, $seconds);
    }
    
    return '00:00:00';
}

关键逻辑说明： #

• 用TIMESTAMPDIFF(SECOND, starttime, endtime)计算每条记录的工作时长（精确到秒），彻底避免精度丢失。
• GREATEST(差值, 0)确保只有工作时长超过阈值的部分才会被累加，不足阈值的记录贡献0加班时长。
• 新增YEAR(starttime)条件，避免同一个月份不同年份的记录被错误统计。

另外，你提供的数据库结构看起来格式混乱，正确的单条工时记录结构应该是这样的（每条行对应一条独立工时）：

如果你的数据库结构确实混乱，建议先整理数据，确保每条工时记录是独立的行。

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