[SOLVED] Counting Time Spent...

[iPixel]

So i've got a database with a field called Duration and it holds values such as 00:00:00 pertaining to time as in Hour:Minute:Second. This is stored as Varchar(##)

 

I have to add these up, basically select all from database based on certain criteria which isnt relevant.

 

Here's how im planning on doing this... tell me if you have a better way.

 

Let's say i get a result of 5.

 

00:12:34

01:33:01

00:17:55

00:20:27

03:55:43

 

I placed all of these in an array to hold onto then im going to explode it by the ":" and add them up separately ending up in

 

04:137:160

 

Then i'll simply do the math and figure out by counts of 60 to see how many minutes are in 160 seconds and add to left and so on and so forth.

 

It's a long annoying proccess and i'll happily take a pointer in a better faster more efficient direction if one exists.

 

Thanks!

[ted_chou12]

i dont know if there is a specific function made for adding up time, but this might give u some ideas:

$times = array(...);// your times.
foreach ($times as $time) {$times1 = explode(":", $time);
$secs[] = $times1[0];
$mins[] = $times1[1];
$hrs[] = $times1[2];}
$sec = array_sum($secs);
$min = array_sum($mins);
$hr = array_sum($hrs);
while ($sec >= 60) {$min ++; $sec = $sec - 60;}
while ($min >= 60) {$hr ++; $min = $min - 60;}
echo "Total time: $hr:$min:$sec";

Ted

 

[PFMaBiSmAd]

If those were a TIME data type in a database, it would be easy, just use the TIME_TO_SEC() function to get the fields into a common number base, SUM() those values, then use SEC_TO_TIME() to get back to a TIME value.

 

Ref: http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

[iPixel]

Here's how i got it working 

 

Basically splite them up by ":"

converted them all into seconds and added them together, than used a formula to convert th hh:mm:ss

 

$timespent = array();
while($res3 = oci_fetch_assoc($parse3))
{
	array_push($timespent,$res3['DURATION']);	
}
//print_r($timespent);

$hours_A = array();
$minutes_A = array();
$seconds_A = array();

// seperate all times by ":" so you can later turn them all into seconds.
foreach($timespent as $duration => $time) 
{
	$temp1 = explode(":", $time); // hh:mm:ss

	array_push($hours_A, $temp1[0]); //hh
	array_push($minutes_A, $temp1[1]); //mm
	array_push($seconds_A, $temp1[2]); //ss
}

// Add All Seconds Together
$time_seconds = 0;
foreach($seconds_A as $sec => $s)
{
	$time_seconds += $s;
}

// Add Minutes Together
$time_min_seconds = 0;
foreach($minutes_A as $min => $m)
{
	$time_min_seconds += $m;
}
// Turn minutes into seconds
$time_min_seconds = ($time_min_seconds * 60); 

// Add Hours Together.
$time_hr_seconds = 0;
foreach($hours_A as $hr => $h)
{
	$time_hr_seconds += $h;
}
// Turn Hours into seconds
$time_hr_seconds = ($time_hr_seconds * 3600);

// Get total seconds.
$timetotalseconds = $time_seconds + $time_min_seconds + $time_hr_seconds;

$hms1 = "";
$padHours = true;

$hours = intval(intval($timetotalseconds) / 3600);
$hms1 .= ($padHours) ? str_pad($hours, 2, "0", STR_PAD_LEFT) . ':' : $hours . ':';

$minutes = intval(($timetotalseconds / 60) % 60); 
$hms1 .= str_pad($minutes, 2, "0", STR_PAD_LEFT) . ':';

$seconds = intval($timetotalseconds % 60); 
$hms1 .= str_pad($seconds, 2, "0", STR_PAD_LEFT);