Sum of hours in PHP/Mysql

Asked

Viewed 232 times

0

I am learning PHP/SQL and I am making a system of accounting hours worked of the employee. The amount of hours worked in the week is reproduced as a floating number in the $hours variable:

  • 0.25 (referring to 25 minutes for example)

But when I register this information in the database, to add to the current number of hours worked in the month, I use this SQL query:

UPDATE tb_usuarios SET horasTrabalhadas = horasTrabalhadas + $horasSemanais WHERE id_funcionario = $id_funcionario

But for example, if the decimal column type Working hours have the value of 11.40 (11h and 40 minutes), when added to the value of hours given in the example, the result will exceed 59 minutes and will be:

  • 11.65 (11.00 and 65 minutes)

How do I get PHP to register a more organized and "correct" value on the system when we’re working with hours? Type 12.05 to represent 12h and 5 minutes in the given example.

php mysql

  • 0.25 hours actually corresponds to 15 minutes (60 times 0.25 is equal to 15). So 25 minutes would be something like 0.416666... hours, and working with periodic tithes is complicated and rounding can cause errors by counting totals, for example. Anyway, maybe you should change the Mysql field to save the total minutes worked.

1 answer

1


I made a solution that solves your problem through recursion.

$horasTrabalhadas = fixHour(11.40); // 11:40h
$horasSemanais = fixHour(0.25); // 25 minutos
$total = fixHour($horasTrabalhadas + $horasSemanais);

echo $total; // Saída: 12.05

function fixHour($num)
{
    $decimal = $num - floor($num); // A função floor arredonda o número para o próximo menor valor inteiro

    if ($decimal >= 0.60) {
        $num = ($num + 1) - 0.60;
        return fixHour($num); // Recursão
    }

    return $num;
}

See the function description in more detail floor in the PHP documentation.

I haven’t run the tests for every possible situation, but I believe in your case it will work perfectly. If you want to test with more values use the ideone.

Browser other questions tagged php mysql

You are not signed in. Login or sign up in order to post.