[SOLVED] Average time help

[EchoFool]

I am having difficulties with my MYSQL query. I am trying to work out a average time from a set of rows which each have their own "Start Date" and "End Date".... so im trying to work out the difference between start and end then find the average time taken beween the start and end from all the rows.. this is what i have tried:

 

Example table:

_____Started On__________ Ended On___________UserID_________TaskID

2008-10-26 11:27:24  2008-10-26 11:26:04---------  1--------- ------- 1

2008-10-26 04:12:42 2008-10-26 04:10:36---------  1--------- ------- 2

2008-10-26 01:21:23 2008-10-26 01:17:19---------  1--------- ------- 3

2008-10-25 20:18:19 2008-10-25 20:11:28---------  1--------- ------- 4

 

<?php
$GET = mysql_query("SELECT UserID, COUNT(TaskID) AS Total, AVG(timediff(CompletedOn,StartedOn)) AS AverageTime FROM usertable WHERE Complete='2' GROUP BY UserID ORDER BY Total DESC,AverageTime DESC LIMIT 20")
Or die(mysql_error());
?>

 

 

Currently though the average time response looks like this:

 

Average Time : 73064.4634146341

 

But i wanted it to reply in days / hours / minutes / seconds. Please help

[mapleleaf]

Looks to me like your start time is after your end time.

It would be easier if you stored time as a timestamp 1235353533 for example. No of secs from 1st jan 1970.

Then you could subtract the time and work out how many hours it was.

[Barand]

SELECT UserID, COUNT(TaskID) AS Total,
SEC_TO_TIME(AVG(TIME_TO_SEC(timediff(CompletedOn,StartedOn)))) AS AverageTime
FROM usertable 
GROUP BY UserID

[EchoFool]

Thank you works a treat!