Turn duration in minutes into an H:m format

KNIME Analytics Platform
1

New to KNIME…looking to take a column that has an integer representing minutes and turn it into an hour:minute column. The minutes column is how many minutes before midnight that an event occurred.

Example: 606 minutes before midnight…

Time = (606/60) = 10.1 hours
Hours = 24 - 10 = 14
Minutes = 0.1 * 60 = 6

Together = 14:06

Sample data attached…how can I do this in KNIME?

Thank you for helping…

DurationtoTime.xlsx (16.2 KB)

2

If I understood right: the event occurred 10 hours and 6 minutes before midnight and you would like to have a column that shows the time when the event occurred. In your example, 13:54 instead of 14:06. Is that right? If yes, this explanation and the example workflow could be helpful:

You first need the Math Formula node two times to make the calculations for the hours and the minutes. Then you have two columns: “Hours” where the first value is 13 and “Minutes” where the first value is 54.

Next, you change the data type of the columns “Hours” and “Minutes” to string using the Number To String node. With the String Manipulation node you can then create a new column “Time” where you combine the values in the columns “Hours” and “Minutes”. The first value of this column is “13:54”.

If you want, you can convert the data type of this column from string to date&time.

Example workflow:
Duration_example.knwf (27.4 KB)

3 Likes
3

Maarit,

This works really well, thank you. I realized I meant “after” midnight, not “before”, but it was easy to figure out this correction from the workflow you provided.

Thank you!

2 Likes
4

Maarit,

Can you explain how this line of code works from the String Manipulation node:

joinSep(":", $Hours$, substr(join(“0”, $Minutes$), length(join(“0”, $Minutes$))-2))

Also, do you have a good resource link you can share for learning how to better write string manipulation lines?

Thanks!

5

Nice to hear that the example works for you.

Unfortunately I don’t have any link for string manipulation but I can explain my line of code:

The idea: I have hours in one column and minutes in one column. I want to combine these into a Time column where the hours and minutes are separated by a colon. In case the Minutes column has a one digit value like 6, I want add a zero before the digit to make it a two digit value like 06. So the time will be shown 14:06 and not 14:6. This is also relevant for the Hours column, but there were no such cases (f.ex. 6:54) in your data set.

Starting from the innerbost brackets:

  • join(“0”, “$Minutes$”): add a zero to all values in the Minutes column -> “054”, “048”, “06” etc.

  • length(join(“0”, “$Minutes$”))-2: the index of the first relevant digit in the values in the Minutes column -> 0 for “06”, 1 for “054”

  • substr(join(“0”, $Minutes$), length(join(“0”, “$Minutes$”))-2): take a substring of the values in the Minutes column starting from the first relevant digit: -> “54”, “48”, “06” etc.

  • joinSep(":", $Hours$, substr(join(“0”, $Minutes$), length(join(“0”, “$Minutes$”))-2)): join the values in the Hours column and the manipulated values in the Minutes column using the separator “:”

1 Like
6

Thank you for the explanation!

1 Like