CODEWITHSUNDEEP
php
andernicken
andernicken

Reputation: 39

Can't manage to calculate time for percent

I have searched around and tested different solutions, but it seems that I don't get it to work.

In MySQL I have a table that list times for a workday: ID, sheet_id, workhours, phonecall, meeting, coffe, education, clean (except ID, sheet_id, all fields are TIME)

First, it doesn't calculate as I want. (I have changed the code below just to be easier to see.)

    $sql = "SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(`workhours`))) AS totalworkhours
    , SEC_TO_TIME(SUM(TIME_TO_SEC(`phonecall`))) AS totalphonecall
    , SEC_TO_TIME(SUM(TIME_TO_SEC(`meeting`))) AS totalmeeting
    , SEC_TO_TIME(SUM(TIME_TO_SEC(`coffe`))) AS totalcoffe
    , SEC_TO_TIME(SUM(TIME_TO_SEC(`education`))) AS totaleducation
    , SEC_TO_TIME(SUM(TIME_TO_SEC(`clean`))) AS totalclean  FROM working WHERE sheet_id = '$working_id'";
    $result = $db->query($sql);
    if ($result->num_rows > 0) {
        while($row = $result->fetch_assoc()) {
$per_workhours = $row['totalworkhours'];
echo "Total workhours: ". $per_workhours."<br />";
$per_phonecall = $row['totalphonecall'];
echo "Total phonecalls: ". $per_phonecall."<br />";

And so on everything seems fine...

Total workhours: 33:00:00
Total phonecalls: 16:53:00
Total coffe: 02:02:00
Total clean: 02:02:00
Total other: 02:02:00
Total other: 02:02:00
Total other: 02:02:00
Total other: 02:02:00

But this give me a big headache:

Calc1 says a total of 12 (12:18) but it should be 12:12

$no_time = $per_coffe + $per_clean + $other1 + $other2 + $other3 + $other4; 
echo "Calc 1: ". $no_time ." (".date('H:i', mktime($no_time)).")<br />";

$per_time = $per_workhousrs - $no_time;
$percent = round(($per_time/$per_workhours)*100);

I did simplify the code for much repeating. But I want to sum all times. Substract non wanted time and calculate real workhours in percent.

Upvotes: 0

Views: 86

Answers (1)

astrangeloop
astrangeloop

Reputation: 1520

As Flying said, if you are adding string values, php will extract the initial digits (in this case the hours part) and add those, discarding everything else.

You can convert your time strings to seconds and back again using these functions (similar to MySQL's TIME_TO_SEC and SEC_TO_TIME functions):

function timeToSeconds($time)
{
    $sign = 1;
    if (strncmp($time, '-', 1) === 0) {
        $time = substr($time, 1);
        $sign = -1;
    }
    list($hours, $minutes, $seconds) = explode(':', $time, 3) + array(0, 0, 0);
    return $sign * (($hours * 3600) + ($minutes * 60) + $seconds);
}

function secondsToTime($seconds)
{
    $sign = '';
    if ($seconds < 0) {
        $sign = '-';
        $seconds = -$seconds;
    }
    $hours = floor($seconds / 3600);
    $seconds %= 3600;
    $minutes = floor($seconds / 60);
    $seconds %= 60;
    return sprintf('%s%02d:%02d:%02d', $sign, $hours, $minutes, $seconds);
}

Your code could then be modified to:

$no_time = timeToSeconds($per_coffe) + timeToSeconds($per_clean) + timeToSeconds($other1) + timeToSeconds($other2) + timeToSeconds($other3) + timeToSeconds($other4);
echo "Calc 1: ". $no_time ." (". secondsToTime($no_time) . ")<br />";

$per_time = timeToSeconds($per_workhours) - $no_time;
$percent = round(($per_time/timeToSeconds($per_workhours))*100);
echo $percent . "%<br />";

Which (using the values in your example) should give:

Calc 1: 43920 (12:12:00)
63%

It might be easier to modify your SQL query to return the values in seconds which you can then add directly in PHP, and format for display using only the secondsToTime() function.

Upvotes: 2

Related Questions