Handling Duration on Excel

[ttsk8der]

Hello Knime community : D I hope you´re doing great.

So, I have a WK which returns some columns with “Duration” values.
I need to do some math with this values on the excel file. when I save the file I got the values like this.

Saving it as a String chain I got the value

But it is a string so I can´t do math with the value.

Is there a way to export this kind of values in a correct format to an excel
file, or any idea of how can I handle this values before saving it?

-N

[rfeigel]

Have you tried the Extract Duration Fields node?

[gonhaddock]

Hello @ttsk8der
I think that your best option is to use a ‘Column Expressions’ node, you can standardize your input to local time format; this coding combined with Regex allows to access any value in string, and aiming to perform math within the node from defined variables as well.

See this example:

Convert duration to HH:MM:SS

Hello @WesselHL , and welcome to the KNIME community. You can test with ‘Column Expressions’ node (Type == Local Time / String): hours = padLeft(floor(column("time_sec")/3600), 2, "0") minutes = padLeft(floor(mod(column("time_sec")/3600, 1)*60), 2, "0") seconds = padLeft(mod(column("time_sec"),60), 2, "0") join( hours , ":" , minutes , ":" , seconds ) BR

In the same way you can use ‘String Manipulation’ nodes, achieving similar result but with more nodes and workflow complexity.

For your input data, a possible configuration would be as follow: you will have the variables: ‘hours’ ‘minutes’ and ‘seconds’ available in code for math uses, or string ensamble as the example case with $time_standardize$

time_standardize = joinSep(":", hours,  minutes, seconds)

Text Processing _ Time_Standardize_from_String – gonhaddock

You can disaggregate the code as well, aiming to generate separated columns for your output excel file. Or you can use standard nodes (‘Math Formula’?) with these columns too.

This isn’t needed from my perspective, as you can code all your math within the node

Please, let me know if further advise is needed.

BR

[ttsk8der]

Hellooo fiends : D

So I found a sloppy solution which is working, I used “Extract Duration Fields” as @rfeigel has suggested, to get the input you showed on your workflow @gonhaddock.

I did this by the hard way because I can´t get the extension of “Column expression” node.

With this solution I have a new question. Is there a way to convert durations over 24 hours to show them as one of the 24 hours of the day?, example.

Now I´m looking for a way to convert 24+ hours into hour 0, 1,2, 3 and so on. into 24 hours range.
Is there a way I can do this from Knime?
Note: this data set reach durations with +260 hours into duration fields.

Best regards
-N

[takbb]

Hi @ttsk8der, to convert the “hours” column to a value between 0 and 23, you can use Math Formula Modulus mod(x,y) function:

mod($hours$,24)

[ttsk8der]

@takbb Thank you it’s exactly what I was looking for.
@gonhaddock, @rfeigel, Thank you too because I find a solution with you Ideas < 3
: D
BR
-N

[takbb]

Hi @ttsk8der , thank you for marking my post as solution, but I think you should award that credit to either @rfeigel or @gonhaddock - who was it it who best pointed you in the direction of where you needed to go? (It’s a pity that only one helpful post can be marked!)

My addition here was only a minor number conversion.