Calculation of hours for point PHP + MySQL

Question

Calculation of hours for point PHP + MySQL

I am creating a table for calculating employee hours worked, but I am not able to do the calculation to know if the correct amount of hours was worked in the day.

In the database is like this:

On the site will be displayed this way:

In the last column, in "hours worked" I need to put the result of the calculation of Input 1 (beginning of the day), output 1 (Lunch), Input 2 (Return of lunch) and output 2 (end of Day)

I used this select to grab all the data from the table:

SELECT p.id_ponto, p.tb_user_id_user, p.tb_ponto_mes_id_ponto_mes, p.dia_ponto, p.dt_entrada1, p.dt_saida1, p.dt_entrada2, p.dt_saida2, u.id_user, u.nm_user
FROM tb_ponto p
INNER JOIN tb_user u on u.id_user = p.tb_user_id_user

I Am displaying the values like this:

echo '<tr><td>' . $item["nm_user"] . '</td>' . 
'<td>' . $item["dia_ponto"] . '/' . $item["tb_ponto_mes_id_ponto_mes"] . '/2017' . '</td>' .  
'<td>' . $item["dt_entrada1"] . '</td>' . 
'<td>' . $item["dt_saida1"] . '</td>' . 
'<td>' . $item["dt_entrada2"] . '</td>' . 
'<td>' . $item["dt_saida2"] . '</td>' . 
'<td>8 Horas</td></tr>';

How can I calculate this?

0

php mysql cálculo

Author: Denis Rudnei de Souza, 2017-12-27

Source

1 answers

score 0 · Accepted Answer

Opa, what you want is the result in hours from these strings. It can be done as follows:

// Faz o cálculo das horas
$total = (strtotime($item['dt_saida1']) - strtotime($item['dt_entrada1'])) + (strtotime($item['dt_saida2']) - strtotime($item['dt_entrada2']));

// Encontra as horas trabalhadas
$hours      = floor($total / 60 / 60);

// Encontra os minutos trabalhados
$minutes    = round(($total - ($hours * 60 * 60)) / 60);

// Formata a hora e minuto para ficar no formato de 2 números, exemplo 00
$hours = str_pad($hours, 2, "0", STR_PAD_LEFT);
$minutes = str_pad($minutes, 2, "0", STR_PAD_LEFT);

// Exibe no formato "hora:minuto"
echo $hours.':'.$minutes;