Impala - Working hours between two dates in impala-CodePudding

I have two time stamps @starttimestamp and @endtimestamp. How to calculate number of working hours between these two Working hours is defined below: Mon- Thursday (9:00-17:00) Friday (9:00-13:00)

Have to work in impala

CodePudding user response：

think i found a better solution.

we will create a series of numbers using a large table. You can get a time dimension type table too. Make it doenst get truncated. I am using a large table from my db. Use this series to generate a date range between start and end date.

date_add (t.start_date,rs.uniqueid) -- create range of dates
join (select row_number() over ( order by mycol) as uniqueid  -- create range of unique ids
from  largetab) rs 
where end_date >=date_add (t.start_date,rs.uniqueid)

Then we will calculate total hour difference between the timestamp using unix timestamp considering date and time. unix_timestamp(endtimestamp - starttimestamp )
Exclude non working hours like 16hours on M-T, 20hours on F, 24hours on S-S.

case when  dayofweek ( dday) in (1,7) then 24 
         when dayofweek ( dday) =5 then 20 
         else 16 end as non work hours

Here is complete SQL.

select 
end_date, start_date, 
diff_in_hr - sum(case when  dayofweek ( dday) in (1,7) then 24 
         when dayofweek ( dday) =5 then 20 
         else 16 end ) total_workhrs

from (
select  (unix_timestamp(end_date)- unix_timestamp(start_date))/3600 as diff_in_hr , end_date, start_date,date_add (t.start_date,rs.uniqueid)   as dDay
from    tdate t 
join (select row_number() over ( order by mycol) as uniqueid from  largetab) rs
where end_date >=date_add (t.start_date,rs.uniqueid) 
)rs2
group by 1,2,diff_in_hr