how to return the average of a sql time field

Question

I have an sql database with a time field.

The runtime time field contains 02:06:24, 00:01:12 etc...

I am trying to return the average of this field in the HH:MM:SS format using;

SELECT AVG( runtime )
FROM `result`

This returns 8312.2342729307 which I thought was the average in seconds however when converted into minutes this is incorrect.

I am able to return the MIN and MAX runtimes in the correct format however cannot get the AVG, how do i do this?

Failing that is there anyway to select the middle element to use as a median average?

Answer 1

So, by way of illustration...

SELECT SEC_TO_TIME((TIME_TO_SEC('02:06:24')+TIME_TO_SEC('00:01:12'))/2) x;
+----------+
| x        |
+----------+
| 01:03:48 |
+----------+

Answer 2

You can't use the AVG() function against a DATETIME/TIME

I am casting DATETIME to DECIMAL( 18, 6 ) which appears to yield a reasonably (+- few milliseconds) precise result.

SELECT cast(CAST( AVG( CAST( runtime AS DECIMAL( 18, 6 ) ) ) AS DATETIME ) as time)
FROM dbo.result;