CODEWITHSUNDEEP
sqlsql-server
Mehmet
Mehmet

Reputation: 1535

SQL Server: hour and minute average

In SQL Server, I have a start time column on a table such as:

2011-09-18 08:06:36.000
2011-09-19 05:42:16.000
2011-09-20 08:02:26.000
2011-09-21 08:37:24.000
2011-09-22 08:22:20.000
2011-09-23 11:58:27.000
2011-09-24 09:00:48.000
2011-09-25 06:51:34.000
2011-09-26 06:09:05.000
2011-09-27 08:25:26.000
...

My question is, how can I get the average hour and minute? I want to know that what is the average start time for this job. (for example 07:22)

I tried something like this but didn't work:

select CAST(AVG(CAST(DATEPART(HH, START_TIME)AS float)) AS datetime) FROM

Thanks.

Upvotes: 2

Views: 2627

Answers (3)

T N
T N

Reputation: 10024

Casting to TIME can simplify the calculations a bit. If you prefer to get an actual TIME value instead of text, the following are options:

SELECT
    CAST(DATEADD(ms, AVG(DATEDIFF(ms, 0, CAST(StartTime AS TIME))), 0) AS TIME(3)) AS AverageTimeMilliseconds,
    CAST(DATEADD(second, AVG(DATEDIFF(second, 0, CAST(StartTime AS TIME))), 0) AS TIME(0)) AS AverageTimeSeconds,
    CAST(DATEADD(minute, (AVG(DATEDIFF(second, 0, CAST(StartTime AS TIME))) + 30) / 60, 0) AS TIME(0)) AS AverageTimeMinutesRounded
FROM T

The last expression uses (AVG(...) + 30) / 60 to round seconds to minutes.

Results:

AverageTimeMilliseconds AverageTimeSeconds AverageTimeMinutesRounded
08:07:38.200 08:07:38 08:08:00

See this db<>fiddle.

Upvotes: 0

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

declare @T table(StartTime datetime)

insert into @T values
('2011-09-18 08:06:36.000'),
('2011-09-19 05:42:16.000'),
('2011-09-20 08:02:26.000'),
('2011-09-21 08:37:24.000'),
('2011-09-22 08:22:20.000'),
('2011-09-23 11:58:27.000'),
('2011-09-24 09:00:48.000'),
('2011-09-25 06:51:34.000'),
('2011-09-26 06:09:05.000'),
('2011-09-27 08:25:26.000')

;with C(Sec) as
(
  select dateadd(second, avg(datediff(second, dateadd(day, datediff(day, 0, StartTime), 0), StartTime)), 0)
  from @T
)
select convert(char(5), dateadd(minute, case when datepart(second, C.Sec) >= 30 then 1 else 0 end, C.Sec), 108)
from C
-----
08:08

Upvotes: 2

Upendra Chaudhari
Upendra Chaudhari

Reputation: 6543

Try this :

select  CAST((SUM(DATEPART(HH, START_TIME) * 60 + DATEPART(MI, START_TIME))/COUNT(*))/60 AS VARCHAR(10)) + ':' + CAST((SUM(DATEPART(HH, START_TIME) * 60 + DATEPART(MI, START_TIME))/COUNT(*))%60 AS VARCHAR(10)) 
FROM.....

Upvotes: 0

Related Questions