I would like to find the average of duration of the times coming in the below format (hhh:mi:ss)
20:32:37
67:55:50
58:36:35
106:32:11
91:30:39
Any help is greatly appreciated
Solved! Go to Solution.
You could do it the long way, multiplying hours by 3600, etc., or simply cast these durations as intervals. For example, you can do this in BTEQ or Studio:
create table times (id int, hms interval hour(3) to second) unique primary index(id);
insert into times values(1, '20:32:37');
insert into times values(2, '67:55:50');
insert into times values(3, '58:36:35');
insert into times values(4, '106:32:11');
insert into times values(5, '91:30:39');
select avg(hms) from times produces: 69:01:34.400000
You could do it the long way, multiplying hours by 3600, etc., or simply cast these durations as intervals. For example, you can do this in BTEQ or Studio:
create table times (id int, hms interval hour(3) to second) unique primary index(id);
insert into times values(1, '20:32:37');
insert into times values(2, '67:55:50');
insert into times values(3, '58:36:35');
insert into times values(4, '106:32:11');
insert into times values(5, '91:30:39');
select avg(hms) from times produces: 69:01:34.400000