How to calculate total time duration 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 <-

[CJesusSaves]

That's because the time values are not accumulated, but instead just re-initiated.

Try addition operators. Example:

$Nss_timestart += $ss_timestart; // $Nss_timestart = $Nss_timestart + $ss_timestart; 
$time1 = "$Nss_timestart";

Hope it helps, if it doesn't please post back for the profit of any one else who wants to know.

[julzk]

Hi, I tried this but it did not work :*(

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.

[CJesusSaves]

When you say it didn't work, what output did you get? If you didn't already, you should accumulate for all:

Edited: It just hit me, you are working with date and time, unless those date/time stamps are not in "integer form" then like you already noticed, the method I posted won't work.

In any order, from your code above, $ss_datestart, $ss_timestart, $ss_dateend , $ss_timeend must be added to their respective previous values. After that, then you can continue to codes on line 48 and after.

It might help if I can see those date/time start/end values. That is, at least show one row result for those values. Then we can figure out how to go about 'adding' them.

Sorry about the previous miss understanding.

1. Use SUM mysql statement for hours and minutes fields.
2. Change minutes to hours (/60) and keep the remainder as minutes (%60).
3. Collate time elements and labels for output.

Simple.
However, placing time as integer (seconds) in your DB would be easier - you should do this.

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

Oh hell, julzk, you didn't even format the date to unix. It's in European(?) format:
dd-mm-yyyy.

For a pure mysql solution:

SELECT FLOOR(SUM(UNIX_TIMESTAMP(CONCAT(STR_TO_DATE([B]end_date[/B],'%d-%m-%Y'), ' ',[B]end_time[/B])) - UNIX_TIMESTAMP(CONCAT(STR_TO_DATE([B]start_date[/B],'%d-%m-%Y'), ' ',s[B]tart_time[/B])))/3600) AS hrs, FORMAT(SUM(UNIX_TIMESTAMP(CONCAT(STR_TO_DATE([B]end_date[/B],'%d-%m-%Y'), ' ',[B]end_time[/B])) - UNIX_TIMESTAMP(CONCAT(STR_TO_DATE([B]start_date[/B],'%d-%m-%Y'), ' ',[B]start_time[/B])))/60%60,0) AS mins FROM [B]datecalc[/B]

Change bold fields/table to your own.

This will give 2 'total' columns:
hrs
mins

If you had proper 'integer' unix timestamps, all this nastiness could be avoided - even if you were using php for the calculation part.

[julzk]

Alrighty, figured out the answer myself :P

Just added the below after my $seconds variable and then I just called $allhourstotal and $minutesleft outside the while loop and it works fine :)

$allhours=$allhours+floor($diff / 3600); // Total Hours
				$allminutes=$allminutes+floor(($diff - $hours * 3600) / 60); // Total Minutes
			
				if ($allminutes >= 60) {
				$sparehours = floor($allminutes / 60);
				$allhourstotal = $allhours+$sparehours;
				$minutesleft = $allminutes-($sparehours*60);
				}

OK