Solved: Re: Average of Duration coming in hhh:mi:ss format

infernalhell

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

GJColeman

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

GJColeman

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