CODEWITHSUNDEEP
phpmysql
Muiter
Muiter

Reputation: 1520

How to make a correct time calculation?

I have an MySQl table with different fields.

Date       Time_in  Time_out Break
2013-12-16 06:00:00 16:30:00 01:15:00

I am able to calculate the time diffrence (10:30) between Time-in and Time_out with

TIME_FORMAT(TIMEDIFF(Time-in, Time_out),'%H:%i') AS pers_in

How do I get the real working time with the time of the break calculated? I have tried

$worked= date('H:i', strtotime($row['pers_in']) - strtotime($row['Break']));

This wil give an working time of 10:15 instead of 9:15. Any suggestions?

Upvotes: 3

Views: 65

Answers (2)

Joachim Isaksson
Joachim Isaksson

Reputation: 181067

You can simply subtract the three times to get the correct value;

SELECT TIME_FORMAT(time_out - time_in - break, '%H:%i') working_time
FROM Table1;

An SQLfiddle to test with.

Upvotes: 2

Mark Reed
Mark Reed

Reputation: 95355

Does this work in the SQL? 

SELECT TIME_FORMAT(TIMEDIFF(TIMEDIFF(Time_in, Time_out), Break), '%H:%i')

Upvotes: 2

Related Questions