Time window for operating hours only

[ali_aff]

I am working on calculating the rent for a machine based on operating hours,
As per database for machine starting date/time is Tuesday 7pm and ending date/time as Monday 6pm, date/time difference calculate this as 143 hours
while the operating hours are 8am to 4pm from Monday to Friday, i.e. only 32 hours

I am unable to figure out how to delete non-operating hours i.e. any time that is after 4pm and before 8am as well as any time on Saturday and Sunday. (143- 32= 111 hours), please guide.

[bruno29a]

Hi @ali_aff , I am not sure I understand what is being done here…

Are you getting different intervals, or the data is always the same each week?

If data is from Tuesday 7pm to Monday 6pm, it looks like there is a gap between Monday 6pm to Tuesday 7pm, meaning a whole 8am to 4pm operating hours on Tuesday. Does that mean there is no operating hours on Tuesday? Then it’s just 8am to 4pm on 4 days of the week, is that how you got 32 hours, even though you said from Monday to Friday which technically would have made 40 hours?

Let’s say it’s 32 hours as you said, then isn’t that the answer already? Why do you want to delete non-operating hours if you already have the operating hours?

Can you please share some data and what results you expect?

[ali_aff]

Yes there are different intervals and various time windows based on and operating hours in every row of data,
In this example, machine was added to database on Tuesday 7pm, operation only started from Wednesday 8am to 4pm, then on Thursday, Friday and Monday for same hours 8am to 4pm ---- meaning it was operated only for 4 days (Wed, Thu, Fri, Mon — 4days x 8 hours = 32 hours), and then removed from database on Monday 6pm.
The node “Date&Time Difference” calculates 143 hours from 7pm on Tuesday to 6pm on Monday, ---- 5 hours for Tuesday, 24 hours for Wed, Thu, Fri, Sat. Sun and 18 hours on Mon — 5+24*5days+18=143 hours.

My objective is to remove any time that doesn’t fall in Operating hours.

There are many other other operating hours as well, e.g.
12 hours for 5 days
18 x 5,
24 x 5,
12 x 7 days,
24 x 7

[bruno29a]

Hi @ali_aff , thanks for the clarification. Now it makes more sense

Could you send some sample data? Does the data contain date ranges? Like, what’s the input data to work with?

In addition, I have this question: is the operation hours always 8hrs per day? And is it always on the weekdays only (any days between Mon and Fri), that is excluding the weekends?

I was wondering, if they’re always 8hrs operation day, then why not just count the amount of week days between the range, and then multiply by 8?

[knimediger]

@ali_aff,

If I understand your problem correctly it is the reverse version of the counting of workdays only Count Weekdays/Workdays
You mentioned that the machines to not run 24h a day and not 7 days a week.

Let’s assume you work Mo-Fr from 8am till 6pm.

So as first step I would identify the amount of days off (weekends) by checking whether the day-of-week (DoW) of start is before DoW of the end (e.g. Mo-Th).
Then calculate the amount of full week between start and end. Now reduce the days between start and end by the amount of days off (two days off per week times amount of weeks. If the DoW of start is past the DoW of end reduce by another weekend.

Now you need to do the same stuff to the workhours based on the amount of days calculated before.

HTH

[takbb]

Hi @ali_aff ,

I approached this on the basis that you had data for each machine provided in the following form, describing for each machine the start and end day of the week and time:

There is also a schedule of “costable/chargeable” operating times that applies to all machines:

The charge for a given machine will be the sum of the hours it is in operation during the chargeable period for each day between the start and end day of its operation.

What I did was “build” a row for each day of operation for each machine, using the “One Row to Many” node, which comes as an extension and is available via the hub if you don’t have it already installed.

After this, each row now has to be adjusted to represent the day of operation, and then the start and end times for the day must be altered in accordance with the operating hours specified for a given day. So if the machine start time is 03:00 but “costable” hours don’t begin until 08:00, then the “costable” start time for the day is adjusted to be 08:00. Likewise a similar change is made for the end time. A further rule is needed for the “in between” days for the operation of the machine, since the machine is deemed to have started operation at the standard “operating start time” for a given day if the machine’s usage started before the particular day. Likewise the end time is the standard “operating end time” for a given day if the machine’s usage ended after the current day.

So in the following, we can see that Machine A, operating from 19:00 and Tue until 18:00 on Monday has the costable start and end time for Tuesday as being the same time, and so there would be zero charge. On the Monday ( and in the intervening days), the chargeable time is rated at just the operating times for the specific days.

We can then calculate the costable minutes for each day for the machine:

and then subsequently group by machine, divided by 60 to get the value in hours, and then join back to the original data set, to arrive at the following result:

I don’t know which KNIME AP you are using. This workflow was done in KNIME 4.4, but there is nothing here that shouldn’t equally work in KNIME 4.3
Operating Hours.knwf (40.9 KB)

[edit - reuploaded as I found a bug in my original requiring a second “rank” node]

[bruno29a]

Hi @takbb , this is assuming that the input data is how you presented it, and that it only covers periods of less than a week per row - still waiting for confirmation from @ali_aff if the data is how you presented, or is it by date/time.

For example, for your Machine B, is that Wed on the same day, or is the end day of Wed a week after? Obviously in your example, you are treating as the same day. But what was the rule for not treating it as a week?

EDIT: Regardless though, even if the input data is given as dates, we would still end up converting the dates to days of the week, and apply what you did, with slight adjustments, but the general idea would be what you implemented once the data is pre-processed to reflect this format.

[takbb]

Hi @bruno29a , yes you are right that I made those assumptions as there wasn’t anything else to go on, and it was taking the simplest case.

But yes, if it needed to be extended to actual dates, then the algorithm I think still works provided it is used in conjunction with a “week counter” that can then apply the require whole number of operating hours for the week, and there would be a few complications along the way but in essence I think it can be tweaked to work as you describe.

[ali_aff]

thanks for your support,
I am trying to implement what @takbb has given, I think that will work for me, but I am not sure yet.
Refer attachment where in four events the “date&time difference” is calculating full difference. In subsequent columns I manually calculated what it should be along with explanation. My objective is only to count Operating hours, exclude all after hours, weekend hours (where applicable) and public holidays.

Also note that since I have close to 5000 events, I am having “Java Heap error” after I incorporated knwf provided by @takbb.

Forum.xlsx (11.6 KB)

[takbb]

@ali_aff

I am having “Java Heap error” after I incorporated knwf provided by @takbb.

Hi @ali_aff , in the directory (folder) for your KNIME installation, there is a file knime.ini

What is the value in there that you have for -Xmx
e.g. in mine (pictured), it is set to 5100m

What version of KNIME are you running, and on what OS?
How much physical RAM do you have on your machine?

[ali_aff]

I have Xmx6500m with 4.3.3 and Win10

@takbb and @bruno29a
did you had a chance to look into the file I shared in previous message — am I clear now on situation and data format I have?

[takbb]

Hi @ali_aff, not really sure why you’d have had the java heap issues. Did you have other workflows open at the same time? I’m using KNIME 4.4, but hopefully that isn’t causing any problems.

Yes I’ve had a look at your spreadsheet, and I’m reviewing the workflow. I notice on your spreadsheet that some manual calcs have a machine being used for 14.5, 17 or maybe 12 hours in a given day, so the operating hours doesn’t appear to apply, which I found confusing. Does that mean there are different operating hours for a given machine, or is it just that the basis for the calculation is different now?

[ali_aff]

Yes, columns D to G are agreed and fixed operating hours/days/time for the given examples. There are many others.
Columns B and C represent database clock time, but not the actual operating time.

[takbb]

So are you saying that there needs to be some way of interpreting columns “D to G”, so that the workflow can determine the operating hours that apply for a given machine?

That’s possibly not too bad if they are all fairly standard (i.e. a set start and end time every day for the period, and it’s always start-day to end-day, but could get a bit tricky if there is anything more complicated.

[takbb]

Hi @ali_aff

I returned to your problem and in the absence of other information I made the assumption that some kind of translation of your spreadsheet column as per my previous question was required Please see attached. This workflow returns the same values as your manual calculations. It requires that you configure certain tables (see the Table Creator nodes).

One contains a list of public holiday dates that you would need to source
One contains a list of translations of the “day descriptions” from your spreadsheet, that you would need to manually set up, as the workflow cannot do this for you.

Once it has the necessary information, it will process your “Machine Schedule” (see Excel Reader node)., and return the calculation

It is quite involved, but I have added descriptions on the nodes that hopefully will help you to see what it is doing.
Operating Hours 2.knwf (182.8 KB)

[ali_aff]

Many thanks for helping me with finding the solution to this.
Your extensive workflow has solved my issue and now it is calculating right number of billable operating minutes/hours.
I am grateful to you @takbb .

[takbb]

Hi @ali_aff, you are welcome and I’m pleased to hear it’s working for you. Please do make sure you test it well as there is always a possibility that I’ve overlooked something, and if you do find anything that isn’t quite working as expected then feel free to post and ask further questions. I’ll do my best to assist.
Best regards.

[takbb]

Hi @ali_aff , I saw last thing yesterday that you had a problem in that the workflow didn’t work if the same machine ID appeared more than once, but it was too late for me last night to assist.

I assume you’ve managed to resolve it now, but just in case, what I was going to suggest was use a row counter, or the row-id to append a suffix to each machine name right at the start of the workflow, thereby making them unique.

I would think that the remainder of the workflow would then work without much alteration. At the end you can then strip off the suffix again, should you wish.

As an example, please see attached. Here a String Manipulation is now placed between the Excel Reader and the rest of the flow, to ensure unique names

It applies a suffix to the machine name as follows:
join($ID (BOQ)$,"#",string($$ROWINDEX$$))

I modified the test data to make “ABC 095” appear twice, so after this suffix, the data becomes as follows:

After that, the workflow should work as before, and at the end, should you need to, you can remove the suffix again

substr($ID (BOQ)$,0 ,indexOfChars($ID (BOQ)$,"#" ) )

The chosen suffix would depend on your data. I chose “#” as something that wouldn’t appear in the machine name, but if it does, then you simply need to choose some other character (or sequence of characters) that won’t already be in the machine name, so that you can strip them off again.

Operating Hours 3.knwf (184.6 KB)

Is that how you’ve resolved it, or did you use a different approach?

best regards.

[ali_aff]

Hi @takbb
Many thanks again for your help,
Yes, I did resolved it by following five steps;

1. I added Grouping Attribute - UniqueGroupingID - in Rank node (Get sequential),
   

   

2. I changed Grouping Attribute of subsequent Rank node (Sequence row by Machine) from Machine to UniqueGroupingID
   

   

3. I added a node “String Manipulation” to get UniqueGroupingID is original data
   

   

4. I also added a node for “Duplicate Row Filter” to have only once the days & Machine combination.
   

   

5. Then for Joiner instead of using columns for ID(BOQ) and Machine, I used UniqueGroupingID.
   

   

also I excluded columns for UniqueGroupingID to get following result

Attached update data file and adjusted knwf
Forum_V1.xlsx (18.3 KB)
Operating hours - adj.knwf (342.1 KB)