AVG(time) problem..please help!

[mcerveni]

Below shows the AHT (average handling time), avg acw, and avg. hold time.

I used the AVG(field) sql function.

The problem? --> It doesn't count the seconds properly. also, i haven't figured out a way to format it as a timestamp yet.

 

 

 

 

 

Query is 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 import_stats.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);

[PFMaBiSmAd]

Time in the format 00:00:00 is a mixed number base and you cannot directly do any math on it. You need to convert it into a single number base (i.e. seconds) in order to do math on the value, then convert it back to a time format. See the mysql TIME_TO_SEC() and SEC_TO_TIME() functions.