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","",$r[ss_service]);
      $ss_comment=str_replace("\r\n","",$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_datestartDate End: $ss_dateend";
	echo "</td>";
	echo "<td valign='top'>";
	echo "Time Start: $ss_timestartTime End: $ss_timeend";
			$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 "";
}
// ##### 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.

[diafol]

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.

[diafol]

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(<strong>end_date</strong>,'%d-%m-%Y'), ' ',<strong>end_time</strong>)) - UNIX_TIMESTAMP(CONCAT(STR_TO_DATE(<strong>start_date</strong>,'%d-%m-%Y'), ' ',s<strong>tart_time</strong>)))/3600) AS hrs, FORMAT(SUM(UNIX_TIMESTAMP(CONCAT(STR_TO_DATE(<strong>end_date</strong>,'%d-%m-%Y'), ' ',<strong>end_time</strong>)) - UNIX_TIMESTAMP(CONCAT(STR_TO_DATE(<strong>start_date</strong>,'%d-%m-%Y'), ' ',<strong>start_time</strong>)))/60%60,0) AS mins FROM <strong>datecalc</strong>

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);
				}

[diafol]

OK