How to calculate total time duration from row results

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
julzk
Forum Newbie
Posts: 23
Joined: Fri Oct 30, 2009 4:42 am

How to calculate total time duration from row results

Post by julzk »

Hi,

I have a query that display's results from my sql database. I have it listing a start time and an end time. And I have a script that calculates the duration of how long the service was an outage or was just degraded. I am wanting to display a time total, so gathering the row results and the script that calculates the duration. How can I make it calculate ALL of the displayed services total time based upon each individual service name? Every time I try, it's only calculating each row's hours and minutes and not counting all of the $hours and $minutes and calculating them to provide a total.

Here's my query thus far:

Code: Select all

// ##### View DB data ##### Start ->
$str = str_replace("\n", "<BR />", $str);
if(!isset($cmd))
{
    $result = mysql_query("SELECT *, DATE_FORMAT(ss_datestart, '%d-%m-%Y') AS ss_datestartf, DATE_FORMAT(ss_dateend, '%d-%m-%Y') AS ss_dateendf 
    FROM tbl_services 
    WHERE ss_datestart 
        BETWEEN '$datemonthdate' 
        AND '$datetodaydate' 
    ORDER BY ss_closed ASC, ss_datestart DESC, ss_timestart DESC");
    
    echo "<table width='100%' cellpadding='0' cellspacing='0' border='1'>";
    echo "<tr>";
    echo "<td valign='top'>Service Name</td>";
    echo "<td valign='top'>RT Raised?</td>";
    echo "<td valign='top'>Service Resolved?</td>";
    echo "<td valign='top'>Date</td>";
    echo "<td valign='top'>Time</td>";
    echo "</tr>";
    
    while($r=mysql_fetch_array($result))
   {
      $ss_id=$r["ss_id"];
      $ss_status=$r["ss_status"];
      $ss_closed=$r["ss_closed"];
      $ss_service=str_replace("\r\n","<br>",$r[ss_service]);
      $ss_comment=str_replace("\r\n","<br>",$r[ss_comment]);
      $ss_rt=$r["ss_rt"];
      $ss_rt_raised=$r["ss_rt_raised"];
      $ss_useropen=$r["ss_useropen"];
      $ss_userclose=$r["ss_userclose"];
      $ss_datestart=$r["ss_datestartf"];
      $ss_dateend=$r["ss_dateendf"];
      $ss_timestart=$r["ss_timestart"];
      $ss_timeend=$r["ss_timeend"];
      
    echo "<tr>";
    echo "<td valign='top'>$ss_service</td>";
    echo "<td valign='top'>$ss_rt_raised</td>";
    echo "<td valign='top'>$ss_closed</td>";
    echo "<td valign='top'>";
    echo "Date Start: $ss_datestart<br>Date End: $ss_dateend";
    echo "</td>";
    echo "<td valign='top'>";
    echo "Time Start: $ss_timestart<br>Time End: $ss_timeend<br>";
            $date1 = "$ss_datestart"; $time1 = "$ss_timestart";
            $date2 = "$ss_dateend"; $time2 = "$ss_timeend";
            $before = strtotime($date1 . " " . $time1);
            $after = strtotime($date2 . " " . $time2);
            $diff = $after - $before;
            $hours = floor($diff / 3600);
            $minutes = floor(($diff - $hours * 3600) / 60);
            $seconds = $diff - $hours * 3600 - $minutes * 60;
    echo "Duration: $hours hours and $minutes minutes";
    echo "</td>";
    echo "</tr>";
    }
echo "</table>";
echo "<br>";
}
// ##### View DB data ##### End <-
julzk
Forum Newbie
Posts: 23
Joined: Fri Oct 30, 2009 4:42 am

Re: How to calculate total time duration from row results

Post by julzk »

Just so we understand, my sql tables do not have a hours/minutes/seconds field. They are some simple variables that calculate the time duration from the ss_timestart and ss_timeend.

When I call $hours and $minutes it simply displays the hours and minutes between ss_timestart and ss_timeend from each row.

But I'm wanting to get the data from $hours and $minutes from the displayed looped rows and then sum a total.
Post Reply