Change time to the earliest time if in the time range

Hi,

I am very new in KNIME please help!! :sneezing_face:

I have a time and date column would like to modify

|Time|Date|
|10:30:00 PM|5/31/2023|
|10:30:00 PM|5/31/2023|
|12:30:00 AM|6/1/2023|
|2:30:00 AM|6/1/2023|
|4:30:00 AM|6/1/2023|
|6:30:00 AM|6/1/2023|
|6:30:00 AM|6/1/2023|
|6:30:08 AM|6/1/2023|
|7:30:00 AM|6/1/2023|
|10:30:00 AM|6/1/2023|
|10:30:00 AM|6/1/2023|
|10:30:00 AM|6/1/2023|

First of all, I would like to change the time into 24 hours format by String to Date & Time node. However, it seems like KNIME does not have a format that matched the date format of mine.

After that, for the time before 6:30:00 am, I would like to change it to 22:30:00 on the day before. For example,

|4:30:00 AM|6/1/2023| would be changed to |22:30:00|5/31/2023|

I think the tricky thing here would be changing the date to yesterday.

Also, for the time between 6:30:00 and 14:30:00, I would like to change it to 6:30:00. For example,

|10:30:00|6/1/2023| would be changed to |6:30:00|6/1/2023|

It is basically to change the time to the earliest time in the time range I have preset.

How to achieve this in KNIME?

Thanks very much for the help in advance!

Hi @caratsweet ,

Here is a workflow that performs all of the steps that you have described. I wasn’t quite sure what you meant by “change the time to the earliest time in the time range I have preset”. This workflow doesn’t look for the “earliest” time but simply hard-codes the “06:30”. It contains all the parts for doing the other manipulations you describe.

In terms of “KNIME does not have a format that matched the date format”, this is a common misconception with the String to Date&Time dialog that the formats are set in stone. The list box provides a number of suggested formats, but you can actually overtype it. It catches a lot of people out because overtyping a list box is not common. In the “good old days”, this would have been presented as a combo-box so that it was more obvious that it was also a text-entry field :wink:


One thing to note on converting times with AM/PM is you need to find a locale that matches the case. My tip is if it is in uppercase (AM/PM), use the locale en-US and if it is in lower case (am/pm) use en-GB (!). Alternatively convert the column to one or other case and just stick with the one locale.


Convert and modify dates and times.knwf (50.5 KB)

Hopefully this flow gives you pointers. If you have questions on how it works, or if it needs adapting, feel free to post further.

The very first “node” that you see is actually a component making it easy to take the delimited data that you supplied into an output table, so don’t be confused by that as this would typically be something like a file or a Table Creator.

4 Likes

Hi @takbb ,

Thanks very much for the help! especially the String to Date&time part. It really clear my doubts and it is exactly what I want.

Sorry for my unclear explanation. “change the time to the earliest time in the time range I have preset” means

For example,

For time between 00:00 and 6:30, would like to change them to 22:30 on the day before

For time between 6:30 and 14:30, would like to change them to 6:30

For time between 14:30 and 22:30 would like to change them to 14:30

For time between 22:30 and 23:59 would like to change them to 22:30

In this case, for Rule-based row splitter, would it be possible to have more splits?

I am trying multiple Row splitter now, would there be a simplier way?

Thank you so much for the help! That really means a lot to me! :grinning:

1 Like

Hi @caratsweet, it’s a pleasure to help, and I now realise my previous workflow missed the changing of the pre 6:30 AM times being changed to 22:30.

Thank you for the additional information. My understanding now is that you have specific preset “bands” of times, and for each band the following shows the action to be taken:

image

If my understanding is now correct, then in this case you would not need to provide additional row splitters, but would simply need to change the rules in the Rule Engine to allow for all of the time adjustments. By moving the rule engine in my previous work flow so that it appears after the concatenation, it can cater for all of the above time adjustments, and the only thing that needs to occur as the result of the row-split, is the day adjustment as before:

The Rule Engine will now have the following rules:
image

$Time$ >= "000000" AND  $Time$ <"063000"  => "223000"
$Time$ >= "063000" AND  $Time$ <"143000"  => "063000"
$Time$ >= "143000" AND  $Time$ <"223000"  => "143000"
$Time$ >= "223000" AND  $Time$ <="235959" => "223000"

Convert and modify dates and times - 2.knwf (50.7 KB)

To help with your question though about having multiple splitters, whilst you cannot add extra “splits” on the Row Splitter, if the situation required multiple branches for the different splits then one option is that you could chain Row Splitters in the following pattern, which I think of as a “waterfall” pattern:

You can see that the “concatenate” node does have the ability to increase the available input ports, simply by click the port modifier “...” on the node itself. In this way you could have a series of different actions on the different branches, which I’ve simulated in this picture with String Manipulation nodes but of course could be Rule Engines or any other sequence of similar transformation nodes.

1 Like

Hi @takbb ,

Thank you so much for the demonstration. This is exactly what I would like to achieve!
It really helps a lot and I learnt how powerful KNIME could be.

Just more for a learning purpose… yes I understand that there could be more than 2 inputs for ‘Concatenate’.

For ‘Rule-based Row Splitter’, would it be possible to have 4 splits in 1 ‘Rule-based Row Splitter’ node?

or i would need to split again with one more ‘Rule-based Row Splitter’? or there is another node could achieve this?

for example, just some modification on this case, for these 4 conditions, would it be possible to be included in 1 ‘Row Splitter’?
image

Thank you so much for the help again!

Hi @caratsweet, no there is no facility to add additional outputs on the Rule Based Row Splitter. It is a simple binary TRUE/FALSE where TRUE (by default) goes to the Top output, and FALSE goes to the lower output.

A scripting node such as Java Snippet or Column Expressions (which if you are new to KNIME, you may need to install) could do the whole logic in a single node, but does require use of script/programming constructs.

Within the low/no codes, if you did not wish to “chain” Rule Based Row Splitters, you could have a series of Rule Based Row Filters with each given one of the rules, and they would then do the same as the upper-port outputs in the above “waterfall” picture I put in my previous post, with each then having a branch terminated again by the concatenate node:

There are other more complex ways, and whether they are worth the greater time invested to write/test depends very much on your use case.

For example, to put all of the rules into a single Rule Engine and have it choose a branch, ideally we’d be able to then just use a “CASE Switch Start node” to have it choose the branch based on a column value in the data. Sadly it isn’t quite that straightforward as the CASE Switch Start node acts only on a variable value and treats all rows the same way based on that value.

To get round this limitation, it can be placed inside a loop, and then the variable can be changed on each iteration, so dealing with different rows in different ways.

You could, for example use a Table Row To Variable Loop Start node, and have all rows handled one-at-a-time by the case switch node. The trouble with using loop like that is that performance takes a dive as the data set grows, which isn’t that noticeable on a small dataset but more than a handful of iterations and you will see how slow it becomes, as 1000 rows will take 1000 times as long as 1 row. So that approach really doesn’t scale.

To optimise this pattern, we can use a Group Loop Start instead. It still has a performance penalty but instead of acting on the rows one-at-a-time, it would act on the rows one group (branch) at a time. Theoretically then, the performance drop off becomes a factor of the number of branches rather than the number of rows, which whilst not as fast as a non-loop option remains much better from a scaling point of view.

As an example, see the section of workflow below. Here it does the same as the previous workflow, but also adds a message based on the branch it runs through.

NB this demo is only to show a possible implementation of branch processing. I have left the other functionality from the earlier workflow, (such as Rule Engine to change times) where it was, and of course in a real world scenario this may be done somewhat differently!

image

Convert and modify dates and times - 3 - group loop and branch demo.knwf (84.4 KB)

1 Like

Hi @takbb ,

Got it! CASE Switch Start could be a possible solution here.

Thanks so much for all the help and detailed explanation. That really means a lot to me.

Thanks a lot!!

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