sqlaverageduration

SQL Average of duration (h:mm:ss)

I'd like to take the average of serveral durations (h:mm:ss) using SQL.

So for instance

3:00:00
0:30:00
1:00:00

should average

1:30:00 -- (90min)

I tried CASTing in various formats with no success.

Thanks for any hint!

I tried CASTing in various formats (float and datetime) with no success.

Solution

  • You have not mentioned which DBMS. Here is the code which works in SQL Server. First I converted the time into seconds and took the average. Later the result is converted back to time.

    DECLARE @test AS TABLE (column1 TIME)

INSERT INTO @test SELECT '3:00:00'
INSERT INTO @test SELECT '0:30:00'
INSERT INTO @test SELECT '1:00:00'

SELECT CONVERT(VARCHAR, DATEADD(SECOND,AVG(DATEDIFF(SECOND, 0,column1)),0),108) FROM @test