How to get average time from a series of DateTime columns in SQL Server 2008?

Question

Let's say that I have a table containing the following - id and date (just to make everything simple). It contains numerous lines.

What would my selection query look like to get the average TIME for these rows?

Thanks,

Answer 1

Disclaimer: There can be a lot .

Notes:

• You cannot use the AVG() function for DATETIME/TIME
• I discard DATETIME in DECIMAL( 18, 6 ) , which apparently gives the exact (+ - a few milliseconds) exact result.

# 1 - Average date

 SELECT CAST( AVG( CAST( TimeOfInterest AS DECIMAL( 18, 6 ) ) ) AS DATETIME ) FROM dbo.MyTable; 

# 2 - Average time - deleting a position by date, painting, and then average

 SELECT CAST( AVG( CAST( TimeOfInterest - CAST( TimeOfInterest AS DATE ) AS DECIMAL( 18, 6 ) ) ) AS DATETIME ) FROM dbo.MyTable; 

The second example subtracts the DATETIME date part from itself, leaving only the temporary part, which is then discarded as a decimal number for averaging and returns to DATETIME for formatting. You will need to cut out part of the date (this is pointless), and the time part should represent the average time in the set.

Answer 2

 SELECT CAST(AVG(CAST(ReadingDate AS real) - FLOOR(CAST(ReadingDate as real))) AS datetime) FROM Rbh 

Answer 3

I know that at least in some SQL standards, the value expression (an argument to the AVG () function) cannot be a datetime value or a string value. I have not read all the SQL standards, but I would be surprised if this limitation loosened over the years.

In particular, this is because the "average" (or arithmetic mean) of the values ​​of "n" is defined as the sum of the values ​​divided by "n". And the expression '01 -Jan-2012 08:00 '+ '03 -Mar-2012 07:53' makes no sense. Also there is no '01 -Jan-2012 08:00 '/ 3.

Microsoft products have a game history quickly and freely with SQL, exposing the internal representation of their date and time data types. Dennis Ritchie would call it "unreasonable implementation tricks."

In earlier versions of Microsoft Access (and possibly in current versions too), you could multiply the date '01 -Jan-2012 'by the date '03 -Mar-2012' and get the actual return value, supposedly in units of square dates.

If your dbms supports the interval data type, then averaging is simple and does what you expect. (SQL Server does not support interval data types.)

 create table test ( n interval hour to minute ); insert into test values ('1:00'), ('1:30'), ('2:00'); select avg(n) from test; avg (interval) -- 01:30:00