How to Find the average of hh:mm:ss in hive

Question

Consider i have hive table with columns script_name, start_time, end_time, duration. Start time, end time and duration are in the format of hh:mm:ss. My requirement is to find the average time of these columns for last 7 days and put into a file.

Answer 1

Convert to unix_timestamp, sum, divide by 3, convert to bigint and convert back to HH:mm:ss:

with data as --Data example. Use your table instead
(select '12:10:30' start_time,'01:10:00' end_time, '02:10:00' duration)

select from_unixtime(cast((unix_timestamp(start_time,'HH:mm:ss')+ unix_timestamp(end_time,'HH:mm:ss')+unix_timestamp(duration,'HH:mm:ss'))/3 as bigint),'HH:mm:ss') from data;

Result:

05:10:10

See test here: http://demo.gethue.com/hue/editor?editor=285484&type=hive

For single column:

Convert to unix timestamp, calculate average in seconds, convert to bigint (average is double, there will be some fraction of second precision loss), and finally convert it back to the string time format:

with data as --Data example. Use your table instead
(select stack(2,'12:10:30','01:10:00') as timeStr)

select from_unixtime(cast(avg(unix_timestamp(timeStr,'HH:mm:ss'))as bigint),'HH:mm:ss') from data;

Result:

06:40:15

See test here: http://demo.gethue.com/hue/editor?editor=285464&type=hive