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 :)
Best Answer
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
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.
See https://docs.microsoft.com/en-us/sql/t-sql/functions/date-and-time-data-types-and-functions-transact-sql?view=sql-server-2017 for more information on date/time datatypes in T-SQL.
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.
Sorry ye my original question wasn't clear enough.
Here is an example from the table
And i want the the avg duration of both columns with as much accuracy as possible
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
