how to calcuate avg time

[mcerveni]

In my mysql table, one of the columns is set as 'time'.

I use the sql function AVG as shown below...

 

$sql3 = "SELECT TeamLeader, AgentName, EmpID, AvayaID, date, SUM(StaffedTime), AVG(AHT) , ACDCalls,  SUM(ACDTime) , AvgACDTime, SUM(ACWTime),  AVG(AvgACWTime),  HoldCalls, HoldTime,  AVG(AvgHoldTime),  OutboundCalls , SUM(OutboundTime) , SUM(AvailTime),  RingTime,  AvgRingTime,  AVG(utilization), SUM(aux1), SUM(aux2), SUM(aux7), SUM(aux8), SUM(aux9) FROM import_stats WHERE EmpID = '$agent' and date BETWEEN '$start' AND '$end' GROUP BY AgentName";
$result3 = mysql_query($sql3) or die(mysql_error());
$row3= mysql_fetch_array($result3);            

//AVERAGES
$utilization = $row3['AVG(utilization)'];
$aht = $row3['AVG(AHT)'];
$acw = $row3['AVG(AvgACWTime)'];
$holdtime = $row3['AVG(AvgHoldTime)'];         

$util = round($utilization);
$aht = round($aht);
$acw = round($acw);
$holdtime = round($holdtime);

 

when i echo those variables, it will show:

 

Avg. AHT: 2936

Avg Hold Time: 465

Avg. Utilization: 57%

 

those numbers are off by a few seconds or by a min. or so.

 

in my mysql table, on column is called AHT. and in that column it shows:

00:20:32

00:19:57

00:20:56

00:26:45

and so on..

 

When i put those numbers in excel and calculate the average, it's a different but correct average.

When i use the sql function AVG(AHT), it produces a wrong average.

 

 

 

 

[Mark Baker]

I assume that your database columns aren't a VARCHAR2 or other character type, but it is a numeric or date/timestamp type

[mcerveni]

yeah the column is set as Time

[Mark Baker]

Can you give us some specific values, what Excel is giving you and what MySQL is giving you for the averages.

 

Note that if you have any NULL values in the data, then you will get differences between MySQL's AVG function and Excel's AVERAGE.... but Excel's AVERAGEA should return the same as MySQL

[shadowfiend20]

i think its happening bcoz of "round"...

[iPixel]

Here is how i get the avg time... I had to do a similar thing for a calling system to check the avg talk time. Basically convert all the time to seconds first then run the mathematics to figure it out.

 

<?php
function GetTotalAvgDuration($category, $ext, $calldate)
{
	$query = mysql_query("SELECT field, field FROM tablename 
						  WHERE cat = '$category' AND extension = '$ext' AND calldate = '$calldate'"
						) or die(mysql_error());
	$rowcount = mysql_num_rows($query);
	//echo $rowcount;

	$timespent = array();
	while($result = mysql_fetch_assoc($query))
		{
			array_push($timespent, $result['duration']); // 00:00:00
		}
	$query_hours = array();
	$query_minutes = array();
	$query_seconds = array();

	foreach($timespent as $duration => $time) 
		{
			$temp = explode(":", $time); //hh:mm:ss

			array_push($query_hours, $temp[0]); //hh
			array_push($query_minutes, $temp[1]); //mm
			array_push($query_seconds, $temp[2]); //ss
		}

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


	// Add All Minutes Together.
	$time_min_seconds = 0;
	foreach($query_minutes as $min => $m)
		{
			$time_min_seconds += $m;
		}
	// Turn Minutes Into Seconds.
	$time_min_seconds = ($time_min_seconds * 60);


	// Add All Hours Together.
	$time_hr_seconds = 0;
	foreach($query_hours as $hr => $h)
		{
			$time_hr_seconds += $h;
		}
	// Turn Hours Into Seconds.
	$time_hr_seconds = ($time_hr_seconds * 3600);

	// Get Total Seconds.
	if($rowcount == 0)
		{
			$timetotalseconds = $time_seconds + $time_min_seconds + $time_hr_seconds;
		}
	else
		{
			$timetotalseconds = (($time_seconds + $time_min_seconds + $time_hr_seconds) / $rowcount);
		}

	$hms3 = "";
	$padHours = true;

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

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

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

	echo $hms3; // Will echo ---  AVG time --  00:00:00
}
?>

[mcerveni]

thanks! this should help a lot.

I'm just a little confused how to implement that to what i have....

 

my query from before is...

 

$sql3 = "SELECT TeamLeader, AgentName, EmpID, AvayaID, date, SUM(StaffedTime), AVG(AHT) , ACDCalls,  SUM(ACDTime) , AvgACDTime, SUM(ACWTime),  AVG(AvgACWTime),  HoldCalls, HoldTime,  AVG(AvgHoldTime),  OutboundCalls , SUM(OutboundTime) , SUM(AvailTime),  RingTime,  AvgRingTime,  AVG(utilization), SUM(aux1), SUM(aux2), SUM(aux7), SUM(aux8), SUM(aux9) FROM import_stats WHERE EmpID = '$agent' and date BETWEEN '$start' AND '$end' GROUP BY AgentName";
$result3 = mysql_query($sql3) or die(mysql_error());
$row3= mysql_fetch_array($result3); 

 

 

now, you're method will save me the hassle of using the Avg function in the query.

 

I just need a bit of explanation on how to get the average AHT (average handling time) , AvgHoldTime, AvgACWTime. and also average of Utilization. Utilization is in a percentage.