Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
madmax88
Creator II
Creator II

Hours between two timestamps (only working hours)

Hi,

I need to calculate the sum of hours between two timestamps:

e.g. Start: 22.06.2019 09:15 until End: 23.06.2019 21:15

Problem:

I only need the hours for:

Monday between Saturday from 08:00 -20:00 and

sunday between 08:00-17:00

In this case it would give me 19:45 hours

22.06.2019 Saturday: 09:15 - 20:00 = 10:45 hours

23.06.2019 Sunday: 08:00 -17:00 = 09:00 hours

Any idea if this would be possible?

Thanks

3 Replies
danilostochi
Creator II
Creator II

dados:
LOAD
interval(Timestamp(data2,'DD/MM/YYYY hh:mm:ss')-Timestamp(data1,'DD/MM/YYYY hh:mm:ss'),'hh:mm:ss') as time

INLINE [
id, data1, data2
01, 22/06/2019 09:15:00, 23/06/2019 21:15:00
];

+55(44) 9 9993-3605, WhatsApp
E-Mail or Skype - danilo16stochi@hotmail.com
madmax88
Creator II
Creator II
Author

Thanks for your reply, but your solution sums the total time. 

In my case I only need the hours between Mon-Sat from 08am-08pm and Sun 08am-5pm over all start and end Timestamps

madmax88
Creator II
Creator II
Author

I have now this load in script: for all days the range 8am to 8 pm.

Is it possible to change it for sunday (08am -5pm,)

TMP:

LOAD  ID,

daystart(DT1)+iterno()-1 as Date,

if(iterno()=1, rangemin(rangemax(frac(DT1),maketime(8)),maketime(20)), maketime(8)) as Start,

if(daystart(DT1)+iterno()-1=daystart(DT2), rangemax(maketime(8),rangemin(frac(DT2),maketime(20))),Maketime(20)) as End

Resident INPUT

while daystart(DT2) >= daystart(DT1)+iterno()-1;

 

left join (INPUT)

LOAD

ID,

interval(sum(End-Start)) as Duration

Resident TMP   group by ID;

 

drop table TMP;