how to calculate time duration total from row results

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

 

// ##### 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 <-

[Buddski]

You need to create a variable OUTSIDE the loop and increment it with each iteration of your loop.

for example.

$sql = "SELECT seconds FROM table";
$sql_query = mysql_query($sql);
$total = 0;
while ($data = mysql_fetch_assoc($sql_query)) {
$total += $data['seconds'];
}
echo 'Total seconds: '.$total;

[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

 

But I'm wanting to get the data from $hours and $minutes from the displayed rows and then sum those.

[Buddski]

Im aware of that.. I just gave you a simple example of what you can do...

for your code you could possibly increment $total with $diff and then display the results after the loop is finished.

[julzk]

Here is what my table looks like:

 

Service-X  Date Start: 12-01-2010  Date End: 12-01-2010  Time Start: 11:18:46  Time End: 19:06:00

Duration: 7 hours and 47 minutes

Service-Y  Date Start: 11-01-2010  Date End: 11-01-2010  Time Start: 10:15:00  Time End: 10:21:00

Duration: 0 hours and 6 minutes

Service-Z  Date Start: 20-12-2009  Date End: 20-12-2009  Time Start: 19:57:51  Time End: 20:53:25

Duration: 0 hours and 55 minutes

 

The duration of each simply calculates the hours/minutes/seconds from each row as per my original script posting above. but I am wanting to re-use my $hours and $minutes variables from inside the while loop for each row and calculate a total hours and minutes from the displayed results.

[Buddski]

Have you actually tried anything I have suggested?

$str = str_replace("\n", "<BR />", $str);
$total = 0;
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;
$total += $diff;
         $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>";
   }
$hours = floor($total/ 3600);
         $minutes = floor(($total- $hours * 3600) / 60);
         $seconds = $total- $hours * 3600 - $minutes * 60;
echo '<tr><td>Total Duration: '.$hours.' hours and '.$minutes.' minutes </td></tr>';
echo "</table>";
echo "<br>";
}