T-SQL calculating average time

Question

I have a problem with calculating average time. This is the case: i have multiple rows, in each row there is data in time format so I need to calculate an average time of all rows and multiply it with number of rows but of course I have a problem with data format because I need to multiply time with integer

Can somebody help me with some advice? thnx Here is some data:

times
00:00:00.7400000
00:00:01.1870000
00:00:00.6430000
00:00:00.6100000
00:00:12.9570000
00:00:01.1000000
00:00:00.7400000
00:00:00.5300000
00:00:00.6330000
00:00:01.6000000
00:00:02.6200000
00:00:01.0300000
00:00:01.9630000
00:00:00.9800000
00:00:01.0170000
00:00:00.7600000
00:00:00.7130000
00:00:00.9730000
00:00:01.0000000
00:00:01.0530000
00:00:02.9400000
00:00:00.8200000
00:00:00.8400000
00:00:01.1800000
00:01:25.8230000
00:00:01.0000000
00:00:00.9700000
00:00:01.2930000
00:00:01.3270000
00:00:13.5570000
00:00:19.3170000
00:00:58.2730000
00:00:01.6870000
00:00:18.7570000
00:00:42.8570000
00:01:12.3770000
00:00:01.2170000
00:00:09.9470000
00:00:01.4730000
00:00:00.9030000
00:00:01.0070000
00:00:01.1100000
00:00:01.6270000
00:00:05.0570000
00:00:00.6570000
00:00:00.7900000
00:00:03.2930000
00:00:00.8600000
00:00:01.0330000
00:00:00.9300000
00:00:00.8730000
00:00:00.9600000
00:00:00.8070000
NULL

so from this data a need average time or/and sum of that data

Answer 1

You can do this by doing a bunch of date arithmetic:

DECLARE @t TABLE(x TIME);

INSERT @t VALUES('00:01:30'),('00:02:25'),('00:03:25');

SELECT CONVERT(TIME, DATEADD(SECOND, AVG(DATEDIFF(SECOND, 0, x)), 0)) FROM @t;

However, what you should be doing is not using TIME to store an interval/duration. TIME is meant to store a point in time (and breaks down if you need to store more than 24 hours, for example). Instead you should store that start and end times for an event. You can always calculation the duration (and the average duration) if you have the two end points.

Answer 2

Assuming you have stored duration in a time field, you could try Datediff function like below to get the sum of durations (or total time). Simply use Avg for average.

Demo: http://sqlfiddle.com/#!3/f8c09/2

select sum(datediff(millisecond,0,mytime)) TotalTime
from t

Answer 3

This may be useful in this and similar scenarios.

Below will convert the date/time to a number of minutes since midnight. In other words time expressed as an integer. You can do an average on that and then convert back to time.

declare @dt datetime = GETDATE();
select DATEDIFF(minute, dateadd(dd, datediff(dd, 0, @dt), 0), @dt);
--DATEDIFF(minute, [date without time i.e. midnight], [date/time of interest])

Answer 4

You should be able to use something similar to the following:

select 
  cast(cast(avg(cast(CAST(times as datetime) as float)) as datetime) as time) AvgTime,
  cast(cast(sum(cast(CAST(times as datetime) as float)) as datetime) as time) TotalTime
from yourtable;

See SQL Fiddle with Demo.

This converts the times data to a datetime, then a float so you can get the avg/sum, then you convert the value back to a datetime and finally a time