Calculating hours with php

Question

Calculating hours with php

Navigation

#1 by (1 votes)
#2 by (-3 votes)

1

How to calculate the time that the person entered and the time that the person left less the interval and display the total hours he gave?

For example if a guy made the following hours: 07:30 12:00 13:30 17:00 would have to display: 8 hours worked . It's just that I have to calculate several days in the same image and add up all the totals of hours I gave and display the result.

Columns in the bank:

bookmarks
id (int)
day (date) "date of time"
type (char) E (input) or S (output)
time (time) "are the schedules"
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 through the code in php:

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 outcome:

Thank you in advance.

php sql

asked by anonymous 03.08.2016 / 15:14

2 answers

Accentuation php - firebird Replacing Wrong Words in a Text Field

score 1 · Answer 1

I noticed that you are using a standard one record in the table for each action. So you need GROUP CONCAT , which will return the seperated data by vírgula .

So a possible solution to calculate would be to use the MYSQL function called TIME_TO_SEC to convert this field TIME and make it a 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 day, 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

So, we could do an ingenious work to calculate the distance between schedules.

  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 from $diferenca_em_segundos to time format.

Then you can use the gmdate function:

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

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

Then the solution would be this:

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

score -3 · Answer 2

Hello

As of PHP 5> = 5.2.0, PHP has the DateTime classes that handle time with various functions. Specifically in your need DateTime :: diff will help you Take a look at the documentation that will be helpful. link

<?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');
?>