T-SQL calculating average time
Solution 1
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
Solution 2
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.
Solution 3
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
user2158645
Updated on July 05, 2022Comments
-
user2158645 almost 2 years
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