calculations with Time(HH:MM:SS) type of column in Hive

Question

I have created a hive table with column avg_response_time having value time in HH:MM:SS. As it is not a timestamp so I have to put this column under the string datatype. Now I want to do some calculations.

Here is the table schema:

create table agent_performance
    (
    S_No int,
    `Date` string,
    Agent string,
    Total_chats int,
    avg_response_time string,
    avg_resolution_time string,
    avg_rating float,
    Total_feedback int
    )
    row format delimited
    fields terminated by ',';

I am adding the image of the dataset.This the how the dataset look like

I want to do some calculations:

1. Total contribution hour for each and every agents weekly basis
2. Average weekly response time for each agent

Answer 1

You can split the hour:min:seconds data based on delimitter :.

And then use it to calculate total response time or resolution time.

also use date_format(current_date(),'W') to calculate week number in a month.

select 
agent,
date_format(`date`,'W') week_no,
sum((split(avg_resolution_time,':')[0]*3600 +split(avg_resolution_time,':')[1]*60+split(avg_resolution_time,':')[2] )/3600) total_weekly_contri_hrs,
avg((split(avg_response_time ,':')[0]*3600 +split(avg_response_time ,':')[1]*60+split(avg_response_time ,':')[2] )/3600) Avg_weekly_response_time_hrs
from agent_performance
group by 
1,2