question

IeuanW avatar image
IeuanW asked

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 :)

queryssmsanalysisavg
10 |1200 characters needed characters left characters exceeded

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

Kev Riley avatar image
Kev Riley answered

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

4 comments
10 |1200 characters needed characters left characters exceeded

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.

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.

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 ·

@ThomasRushton

@Kev Riley

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

0 Likes 0 ·
example.png (19.4 KiB)
Oleg avatar image
Oleg answered

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

10 |1200 characters needed characters left characters exceeded

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

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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