mysql-php计算表中新列的总小时数

Question

im trying to calculate the total hour, i insert the start hour and then i update the end hour of the id:

UPDATE worked_hours
set End= '$end'
WHERE Id= '$id'

So just before the end hour is updated i want to calculate the total hour in all table records. (end - start = total). Thanks i will aprecciate some help with this and also i hope i was clear with my question.

ID | Worker |  Start  |   End    | Total  |
1  |  Jhon  |11:25:40 | 14:04:53 |2:39:31 |
2  |  pete  |10:26:00 | 14:04:53 |3:40:53 |
3  |  David |11:35:30 | 19:04:38 |7:29:08 |

Answer 1

To expand on the suggestion by @spencer7593 in a comment TIMEDIFF will accomplish the calculation.

Query:

SELECT TIMEDIFF('14:04:53', '11:25:40');

Result:

02:39:13

On the PHP update query side, you can update the total at the same time as the End time by using:

$query = "UPDATE `worked_hours` AS wh
SET `wh`.`End` = '$End',
    `wh`.`Total` = TIMEDIFF('$End', `wh`.`Start`)
WHERE `wh`.`ID` = '$ID'";

---

I also highly recommend that you utilize prepared statements

$query = 'UPDATE `worked_hours` AS wh
SET `wh`.`End` = ?,
    `wh`.`Total` = TIMEDIFF(?, `wh`.`Start`)
WHERE `wh`.`ID` = ?'

$stmt = $dbh->prepare($query);
$stmt->execute(array($End, $End, $ID));