Calculation of hours with php

Question

How to calculate the time the person entered and the time the person left minus the interval and display the total of hours they gave?

For example if a guy made the following schedule: 07:30 12:00 13:30 17:00 would have to display: 8 hours worked. Only I have to calculate several days equal in the image and sum up all the total hours it gave and display the result.

Columns on the bench:

markings
id(int)
day(date) "date of time"
type(char) E(input) or S(output)
time(time) "are the times"
collaborator_id(int)

I have the following sql query:

$sql= mysql_query("SELECT dia, GROUP_CONCAT(hora) FROM marcacoes WHERE colaborador_id = {$colaborador_id} AND dia between  '{$periodoInicial}' and  '{$periodoFinal}' GROUP BY dia ");

The display is done using php code:

while($exibe = mysql_fetch_assoc($sql)){                
$tabela ='<tbody>'; 
$tabela .='<tr>';
$tabela .='<td>'.date("d-m-Y", strtotime($exibe['dia'])).'</td>'; 
$tabela .='<td>'.$exibe['GROUP_CONCAT(hora)'].'</a>'.'</td>';
$tabela .='</tr>';
$tabela .='</tbody>';   
echo $tabela;           
}   

Returns the following table:

Desired result:

Thanks in advance.

Answer 1

I noticed that you are using a pattern of a record in the table for each action. That’s why you needed the GROUP CONCAT, which at a time will return the data set by vírgula.

Then, a possible solution to calculate would be using the MYSQL function called TIME_TO_SEC to convert that field TIME and make it one INTEGER.

SELECT dia, TIME_TO_SEC(hora) FROM marcacoes 

The result of the query would be something like this:

 dia        | hora
 2016-09-01 | 27000 (equivalente a 07:30)
 2016-09-01 | 43200 (equivalente a 12:30)
 2016-09-01 | 48600 (equivalente a 13:30)
 2016-09-01 | 63000 (equivalente a 17:30)

With GROUP CONCAT and GROUP BY dia, it would look like this:

 SELECT dia, GROUP_CONCAT(TIME_TO_SEC(hora)) as grupo_hora FROM marcacoes

 dia        | grupo_hora
 2016-09-01 | 27000,43200,48600,63000

Thus, we could make an ingenious work to calculate the distance between the times.

  list($entrada, $almoco_entrada, $almoco_saida, $saida) = explode(',', $resultado['grupo_hora']);

  $diferenca_em_segundos = $saida - $entrada - ($almoco_saida - $almoco_entrada);

Now we have a small problem: The conversion of $diferenca_em_segundos for the time format.

Then you can use the function gmdate:

gmdate("H:i", $diferenca_em_segundos); // 08:00

The only problem with gmdate in this case, it can return unexpected values if the value of the seconds passes a sum of 24 hours.

Then that would be the solution:

How to get the format in hours when it exceeds 24?

Answer 2

Hello

As of PHP 5 >= 5.2.0, PHP relies on Datetime classes that deal with time with various functionalities. Specifically in your need Datetime::diff will help you Take a look at the documentation that will be useful. http://php.net/manual/en/datetime.diff.php

<?php
$datetime1 = new DateTime($dataDoMysql1); // ex: 2016-08-01 06:00:00
$datetime2 = new DateTime($dataDoMysql2); // ex: 2016-08-03 10:00:00
$interval = $datetime1->diff($datetime2);
echo $interval->format('%R%a days');
?>