Ad:

PHP Discussion Thread
Marked Solved
Views: 2955

Jan 17th, 2010

How to calculate total time duration from row results

Expand Post »

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:

php Syntax (Toggle Plain Text)
// ##### 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 <-
// ##### 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 <-

Similar Threads

Calculate Sum of Two Time values in VB.NET
Get video file total time of playing using php in PHP
how to calculate the time duration of wav file in minutes in ASP.NET
calculate the execution time of a program in C
how to add time duration in php in PHP

julzk

Reputation Points: 10

Solved Threads: 2

Light Poster

Offline

28 posts
since Nov 2009

Permalink

Jan 17th, 2010

Re: How to calculate total time duration from row results

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

Try addition operators. Example:

PHP Syntax (Toggle Plain Text)
$Nss_timestart += $ss_timestart; // $Nss_timestart = $Nss_timestart + $ss_timestart; 
$time1 = "$Nss_timestart";
$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.

Last edited by CJesusSaves; Jan 17th, 2010 at 1:08 am.

CJesusSaves

Reputation Points: 16

Solved Threads: 5

Light Poster

Offline

49 posts
since Jan 2010

Permalink

Jan 17th, 2010

Re: How to calculate total time duration from row results

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.

julzk

Reputation Points: 10

Solved Threads: 2

Light Poster

Offline

28 posts
since Nov 2009

Permalink

Jan 17th, 2010

Re: How to calculate total time duration from row results

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.

Last edited by CJesusSaves; Jan 17th, 2010 at 4:22 am.

CJesusSaves

Reputation Points: 16

Solved Threads: 5

Light Poster

Offline

49 posts
since Jan 2010

Permalink

Jan 17th, 2010

Re: How to calculate total time duration from row results

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.

ardav

Sponsor

Featured Poster

Reputation Points: 1041

Solved Threads: 935

Sarcastic Poster

Offline

6,626 posts
since Oct 2006

Permalink

Jan 17th, 2010

Re: How to calculate total time duration from row results

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.

julzk

Reputation Points: 10

Solved Threads: 2

Light Poster

Offline

28 posts
since Nov 2009

Permalink

Jan 17th, 2010

Re: How to calculate total time duration from row 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:

(Toggle Plain Text)

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

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.

ardav

Sponsor

Featured Poster

Reputation Points: 1041

Solved Threads: 935

Sarcastic Poster

Offline

6,626 posts
since Oct 2006

Permalink

Jan 19th, 2010

Re: How to calculate total time duration from row results

Alrighty, figured out the answer myself

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

PHP Syntax (Toggle Plain Text)
				$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);
				}
				$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);
				}

julzk

Reputation Points: 10

Solved Threads: 2

Light Poster

Offline

28 posts
since Nov 2009

Permalink

Jan 19th, 2010

Re: How to calculate total time duration from row results

Last edited by ardav; Jan 19th, 2010 at 9:08 pm.

ardav

Sponsor

Featured Poster

Reputation Points: 1041

Solved Threads: 935

Sarcastic Poster

Offline

6,626 posts
since Oct 2006

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in PHP Forum Timeline: Category class with unlimited depth

Next Thread in PHP Forum Timeline: input data to an array

DaniWeb Message

Cancel Changes

User Name
Password