Average duration of a time(7) column?

Question

question

IeuanW asked Jul 24, '18

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

query ssms analysis avg

10 |1200

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

Answer 1 · 2018-07-24T13:47:03Z

Kev Riley answered Jul 24, '18

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

10 |1200

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

ThomasRushton ♦♦ commented · Jul 24, 2018 at 02:42 PM

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 ·

IeuanW commented · Jul 24, 2018 at 02:02 PM

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 ·

Kev Riley ♦♦ commented · Jul 24, 2018 at 02:28 PM

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 ·

IeuanW Kev Riley ♦♦ commented · Jul 24, 2018 at 05:23 PM

@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 ·

example.png (19.4 KiB)

Answer 2 · 2018-07-26T12:56:14Z

Oleg answered Jul 26, '18

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

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

question