从日期时间计算加班时间

Question

I am trying to calculate overtime based on a overtimefactor of 07:24:00:0000 hours

My database looks like this

| id  |user_id|     starttime       | endtime
|  0  |   1   | 2018-05-09 04:30:00 | 2018-05-09 017:30:00
|  1  |   1   | 2018-05-10 06:30:00 | 2018-05-10 017:30:00
|  2  |   1   | 2018-05-11 04:30:00 | 2018-05-11 015:30:00

I am having a hard time figuring out the selectstatement that would do the job - I want ALL combined overtime as a result

My function i have now looks like this:

public function getOvertimeTotal($id, $selectedMonth) {
      $this->db->query("SELECT 
      COUNT(SUBTIME(TIMESTAMPDIFF(HOUR, starttime, endtime), '07:24:00:0000')) as overTimeTotal
      FROM hours
      WHERE user_id = :id
      AND month(starttid) = :selectedMonth");

      $this->db->bind(':id', $id);
      $this->db->bind(':selectedMonth', $selectedMonth);

      $row = $this->db->single();

      return $row;

    }

What am i doing wrong?

Answer 1

This should help you calculate overtime in fractional hours,

SUM((TIMESTAMPDIFF(SECOND, starttime, endtime) - TIME_TO_SEC('07:24:00:0000')) / 3600)

SQLFiddle link

---

If you need the value converted back in time remove division by 3600 part from above and use SEC_TO_TIME() function

SEC_TO_TIME(SUM(TIMESTAMPDIFF(SECOND, starttime, endtime) - TIME_TO_SEC('07:24:00:0000')))