How to calculate time between two timestamps ignoring some parts of the day (During load)

[#1]

Hello.

I need to calculate how long time has passed between two timestamps. But I must only count time during working hours.

I want to calculate this duration during load for each row in a table that has a start and stop timestamp. (around 500 000 rows)

The time period can be several days, or just minutes. It can start or stop within work hours or outside work hours. Work hours are different depending on what day it is.

Example:

Time during the day that should be counted as work hours

..

..

3/6/2015  08:30 - 22:00

4/6/2015  08:00 - 22:00

5/6/2015  08:00 - 22:00

6/6/2015  10:00 - 18:00

7/6/2015  10:00 - 18:00

...

start_time = 4/5/2015 23:00

stop time = 6/6/2015 11:30

Total time: 15hours and 30 minutes

(08:00 to 22:00 = 14h + 10:00 to 11:30 = 1,5h sums up to 15,5h)

I realize that i need to store the day time hours in a table in some way.

And then somehow I need to calculate the time between start and stop and subtracting the times that are not work hours. But I can't come up with a good way to do this in a QlikView load script.

The time delta needs to be calculated with a precision of minutes.

Thanks in advance

Andreas

[maxgro · Accepted solution]

try here

Calculate hours between two Date/Time strings

or here

Re: Calculate Elapsed Time Nett

[martin_dideriks]

Hi Andreas

I did this on a SQL server not that long ago. It was a pretty straight forward task back then.

I have been thinking how this could be done in Qlik ETL.

There are many ways this could be done. One thing you could do, is to create all the minutes in an interval, and then do an innerjoined intervalmatch. But i think this could be a heavy task to do, if you have many rows to work with.

Attached is a solution, where the idea is to calculate the time per day and then sum this up per FromTime/ToTime.

My solution is based on an excel spreadsheet where you define the workhours per day. I will suggest that you autogenerate this instead.

Hope you can use this for inspiration.

//Martin