Date&Time Difference (working period)

KNIME Analytics Platform
1

Hi,
I have 2 date to compare in order to calculate the difference based on working hours (instead of 24h based interval).

Working hours are:
morning → 09:00 - 13:00
afternoon → 14:00 - 18:00
from monday to friday

example:
Start: Friday @ 10:00
End: Tuesday @ 16:00

With the standard Date&Time node I obtain 101 hours.
I need that the result is: 21 hours

  • 7 on friday (from 10:00 to 13:00 + from 14:00 to 18:00)
  • 8 on monday (from 9:00 to 13:00 + from 14:00 to 18:00)
  • 6 on tuesday (from 09:00 to 13:00 + from 14:00 to 16:00)

Anyone know a solution?

Many thanks in adavance,
Gery.

2

Hi @Gery , you mentioned having to compare 2 “dates”.

You example does not have any dates, but rather 2 days with a time. Can you show us what the real input would look like?

If it is indeed how you showed, how do we know that Tuesday means the Tuesday of the next week and not in 2 weeks? And if I get Tuesday to Tuesday, is it Tuesday of next week? Or would it depend on the time (start Tuesday @ 11:00 end Tuesday @ 16:00 vs end Tuesday @ 10:00)?

And can we get a Start or End value that’s outside the working hours? For example could an End value be on a Saturday, or Friday @ 19:00?

3

Hi @bruno29a,
I re-write the example with more details:

example:
Start: Friday 25th of march @ 10:00
End: Tuesday 29th of march @ 16:00

With the standard Date&Time node I obtain 101 hours.
I need that the result is: 21 hours

  • 7 on friday (from 10:00 to 13:00 + from 14:00 to 18:00)
  • 8 on monday (from 9:00 to 13:00 + from 14:00 to 18:00)
  • 6 on tuesday (from 09:00 to 13:00 + from 14:00 to 16:00)

Thanks again,
Gery.

4

No, the start and the end date&time are always inside the working period. I have to manage the bank holiday days when it falls between start and end, but we could keep it on the side at this time.

Gery

5

Thanks for the additional info @Gery , it helps a bit better :slight_smile:

One more question: Can the Start and End be more than a week apart?

For example, can we have:
Start Friday 18th of March @ 10:00
End: Tuesday 29th of March @ 16:00

6

Yes, it is possible.

Tx,
Gery

7

Thanks again for the additional info @Gery

8

Hi @Gery , sorry I was kinda busy today. I already had a idea how to do this, but did not have the time to implement it. I’ve done it now.

Here’s the general idea that I used.

  1. Get number of full days between the 2 date&time
  2. Get number of weekend between the 2 date&time
  3. With the above, I am able to get number of full working days
  4. Get number of working hours for the start date
  5. Get number of working hours for the end date
  6. Add results for #3 and #4 and #5 gives the total working hours.

Of course, I do take care of cases where the 2 date&time are on the same day, etc… where #3 and #4 and #5 are adjusted accordingly.

I wasn’t sure how your input was, I used full date&time values for my demo. I added a few varieties to try to break the workflow, but it looks to work just fine.

This is my input data:
image

Results:
image

Your example is on the 1st line, which gives 21 hours.

If you notice the 2nd line, the start date is simply a week before the 1st line with the same end date, therefore you expect 40 more hours (8hrs per day, a week’s of working days is 5 days, that makes 40hrs), and the results is 61 hours, which is expected.

Here’s what the workflow looks like:

image
image1114×312 18.1 KB

You will notice that I have parameterized the workflow where you can easily change the times in the future:
image

Probably most of these individual calculations could have been done in the same one Column Expressions, but I did it this way so we could visualize the numbers that were being generated.

Similarly, in the Column Expression, you will see 4 expressions. Only the last one is needed, the other 3 are used in the 4th Expression, but I did it this way so we could see the values of these 3 expressions in their own separate columns - they get removed anyways with the Column Filter.

Here’s the workflow: Date and Time difference based on working hours.knwf (32.3 KB)

1 Like
9

Hi @Gery , sorry, there was a mistake in the logic when start and end was on same day, as you can see in the screenshot of my previous post for Row 3.

I have corrected this. I also added 2 more samples, for the same date but from morning to morning, and afternoon to afternoon (the Row 2 is a case of same day from morning to afternoon).
Results:
image

Here’s the workflow with the corrected logic: Date and Time difference based on working hours.knwf (32.4 KB)

2 Likes
10

Many thanks @bruno29a !
I will check ti shortly.

Bye
Gery

11

Hi @Gery , did the workflow work?

12

Hi @Gery , it would be nice to know if the workflow worked.

13

@Gery Thanks for the workflow. I was looking at it to try to lean more about dates and how to change them for a problem I’m trying to solve. Your example workflow worked fine for me :smiley:
Still need to learn more to solve my problem though :frowning:

14

Hi @Hasenstrauch , you mean MY example workflow. @Gery has not provided anything here, not even confirmation that my workflow worked for his issue, even though I asked for it a few times. But I’m glad that it at least worked for you.

15

@bruno29a Oh so sorry I took the wrong name.
Yes your workflow. It imported without issue and I could work my way thought each node and see/understand what/how you had configured it. Worked without issue.

1 Like
16

No problem @Hasenstrauch , thanks for confirming.

I built it so that it’s configurable. The days are broken into 2 parts as per the original request, but even if your days are not broken down and are just a whole day, you can just do 9-12pm, and 12pm-5pm, no break in between.

1 Like
17

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.