How to return the average value of a sql time field

Question

I have a sql database with a time field.

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

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

 SELECT AVG( runtime ) FROM `result` 

This returns 8312.2342729307 , which I thought was the average in seconds, however, when it is converted to minutes, this is not true.

I can return MIN and MAX runtimes in the correct format, but I can’t get AVG, how can I do this?

Failure, that in any case, choose the middle element to use as the average mean?

Answer 1

So, as an 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 cannot use the AVG () function for DATETIME / TIME

I discard DATETIME in DECIMAL (18, 6), which apparently gives the exact (+ - a few milliseconds) exact result.

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