CODEWITHSUNDEEP
sql-server-2008
Sergio
Sergio

Reputation: 9917

How do I get the average time from a series of DateTime columns in SQL Server 2008?

Lets say I have a table that contains the following - id and date (just to keep things simple). It contains numerous rows.

What would my select query look like to get the average TIME for those rows?

Thanks,

Upvotes: 4

Views: 9104

Answers (3)

Tim M.
Tim M.

Reputation: 54377

Disclaimer: There may be a much better way to do this.

Notes:

  • You can't use the AVG() function against a DATETIME/TIME
  • I am casting DATETIME to DECIMAL( 18, 6 ) which appears to yield a reasonably (+- few milliseconds) precise result.

#1 - Average Date

SELECT 
    CAST( AVG( CAST( TimeOfInterest AS DECIMAL( 18, 6 ) ) ) AS DATETIME ) 

FROM dbo.MyTable;

#2 - Average Time - Remove Date Portion, Cast, 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 date portion of the DATETIME from itself, leaving only the time portion, which is then cast to a decimal for averaging, and back to a DATETIME for formatting. You would need to strip out the date portion (it's meaningless) and the time portion should represent the average time in the set.

Upvotes: 1

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95572

I know that, in at least some of the SQL standards, the value expression (the argument to the AVG() function) isn't allowed to be a datetime value or a string value. I haven't read all the SQL standards, but I'd be surprised if that restriction had loosened over the years.

In part, that's because "average" (or arithmetic mean) of 'n' values is defined to be the sum of the values divided by the 'n'. And the expression '01-Jan-2012 08:00' + '03-Mar-2012 07:53' doesn't make any sense. Neither does '01-Jan-2012 08:00' / 3.

Microsoft products have a history of playing fast and loose with SQL by exposing the internal representation of their date and time data types. Dennis Ritchie would have called this "an unwarranted chumminess with the implementation."

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

If your dbms supports the "interval" data type, then taking the average is straightforward, and does what you'd expect. (SQL Server doesn't 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

Upvotes: 0

Ian Boyd
Ian Boyd

Reputation: 256731

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

Upvotes: 0

Related Questions