How to calculate the average of a Time column in SQL-CodePudding

I have an ID column, and a time column. I want to group the IDs by average time. IDs: 1234, 1234, 5678, 5678 Times: 13:21, 19:55, 14:25, 15:04

select ID, 
avg(cast(CONCAT(left(cast(Time as varchar),2),substring(cast(Time as varchar),4,2)) as int)*1.0)

It does return a result, but I don't believe the average to be correct as the average time can be outside of normal time constraints (aka the minutes can be > 59).

CodePudding user response：

time stores a point in time, not a duration. What would you do for a duration longer than a day? You should instead store either the duration in seconds, minutes, what have you, and format it as hh:mm etc. when you want to display it. Or better yet, store a start date and end date, which is more complete information, and you can always derive the duration (in whatever format you like) from that.

Anyway, dealing with what you have, and assuming this table and sample data:

CREATE TABLE dbo.BadChoices
(
  ID int,
  DurationWithWrongType time(0)
);

INSERT dbo.BadChoices(ID, DurationWithWrongType) VALUES
(1234, '13:21'),
(1234, '19:55'),
(5678, '14:25'),
(5678, '15:04');

You could I suppose do:

SELECT ID, AvgDuration = CONVERT(DECIMAL(10,2), 
    AVG(DATEDIFF(MINUTE, '00:00', DurationWithWrongType)*1.0))
  FROM dbo.BadChoices
  GROUP BY ID;

Output:

ID	AvgDuration
1234	998.00
5678	884.50

Example db<>fiddle

If you want the display to be HH:MM, and you know for sure your durations will always be < 24 hours, you could do:

;WITH src AS
(
  SELECT ID, AvgDuration = CONVERT(DECIMAL(10,2), 
    AVG(DATEDIFF(MINUTE, '00:00', DurationWithWrongType)*1.0))
  FROM dbo.BadChoices
  GROUP BY ID
)
SELECT ID, AvgDuration, 
  AvgDurHHMMSS = CONVERT(time(0), DATEADD(SECOND, AvgDuration*60, '00:00'))
FROM src;

Output:

ID	AvgDuration	AvgDurHHMMSS
1234	998.00	16:38:00
5678	884.50	14:44:30

Example db<>fiddle

CodePudding user response：

We have to cast to datetime to be able to cast to float. We can then find the average and cast back to datetime and then back to time.
A second alternative is to convert the time into minutes, get the average and then use dateadd() and cast back to time

create table times(
t time);
insert into times values
('13:21'), 
('19:55'), 
('14:25'), 
('15:04');
GO

4 rows affected

select 
  cast(
  cast(
  avg(
  cast(
  cast(t 
    as datetime)
    as float)
    )
    as datetime)
    as time)
from times
GO

| (No column name) |
| :--------------- |
| 15:41:15         |

select
  cast(
  dateadd(second,
  avg(
  DateDiff(second,0,t)
    ),
    2000-01-01)
    as time)
from times
GO

| (No column name) |
| :--------------- |
| 15:41:15         |

db<>fiddle here