# question

## Average duration of a time(7) column?

I have 2 columns (TotalDuration/ BartecDuration) and i need to get the avg duration of both column.

So far i have this

```Select

CONVERT(VARCHAR(255), CAST(AVG(CAST(CAST(TotalDuration AS DATETIME) AS DECIMAL(10,5))) AS DATETIME), 108) as TotalDuration,
CONVERT(VARCHAR(255), CAST(AVG(CAST(CAST(BartecDuration AS DATETIME) AS DECIMAL(10,5))) AS DATETIME), 108) as BartecDuration

from WasteCollectionStats
```

But the problem is i am only get the HH:MM:ss, and i need the milliseconds too.

Any help would be greatly appreciated :)

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

·

That's because you are using style 108 - you need to use style 114

https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

·

Also, the DATETIME datatype only stores milliseconds, so you'll lose a lot of accuracy there.

DATETIME2 might be closer to what you need, but even that is limited to 100ns accuracy.

1 Like 1 ·
·

Great thank you. Is it possible to get a more accurate time.

The response I'm getting now is - 00:00:04:320 but the orignal data looks like this - 00:00:02.6115307

0 Likes 0 ·
·

You are losing precision because of all the conversions. Certainly going to a datetime will lose the nanoseconds, but also using the final convert, there is no style that supports fractional microseconds. However you will find that you cannot use the AVG() function with TIME datatype.

Your initial question was that you wanted milliseconds - but now you want nanoseconds too?

Also I'm not sure why you are converting to a decimal(10,5).

Can you post some example input values, and expected output.

0 Likes 0 ·

I know I am a bit late for this train, but here is my solution which preserves the precision of the time(7) data type, which is 100 nanoseconds as @ThomasRushton has already mentioned in his comment. The problem with the query in question is that it is based on the conversion of the datetime to decimal and the former has the 3 milliseconds precision with rightmost millisecond digit limited to have only 3 possible values, namely 0, 3, and 7. At the same time, the attempt to increase the precision by using datetime2 will not work as the conversion from datetime2 to decimal is, naturally, not allowed (it will not make any sense to allow such conversion).

Instead, it is easy enough to convert the time(7) value into decimal format manually, by calculating the total number of seconds and preserving the decimal part representing the 100s nanoseconds (hours part times 3600 plus minutes part times 60 plus all seconds plus entire decimal part). Once the time(7) is converted like this and then aggregated, the resulting decimal number need to be unwound back to time(7). Here is the script:

```;with means as (
select
avg(
datepart(hour, TotalDuration) * 3600. + datepart(minute, TotalDuration) * 60. +
datepart(second, TotalDuration) + datepart(ns, TotalDuration) / 1000000000.
) TotalAsDec,
avg(
datepart(hour, BartecDuration) * 3600. + datepart(minute, BartecDuration) * 60. +
datepart(second, BartecDuration) + datepart(ns, BartecDuration) / 1000000000.
) BartecAsDec
from WasteCollectionStats
)
select
-- TotalAsDec, BartecAsDec,
cast(
format(cast(TotalAsDec as int) / 3600, '00:') +  -- hours part
format(cast(TotalAsDec - 3600 * (cast(TotalAsDec as int) / 3600) as int) / 60, '00:') + -- minutes part
format(TotalAsDec % 60, '00.0000000') as time(7) -- seconds plus whatever decimals part
) AvgTotalDuration,
cast(
format(cast(BartecAsDec as int) / 3600, '00:') +
format(cast(BartecAsDec - 3600 * (cast(BartecAsDec as int) / 3600) as int) / 60, '00:') +
format(BartecAsDec % 60, '00.0000000') as time(7)
) AvgBartecDuration
from means;```

Hope this helps.

Oleg

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.