Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
];
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
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;