Duration in digits

[Karlygash]

Hello, Dear KNIME users!

I have a question, I am taking difference of two dates as a duration, but I have to represent it in digits (in number) in order to take some of them
Can you suggest me some hints, please
For example, 3H 15M => 3.15(in number)

Here I have taken the difference of start_date and end_date

[aworker]

Hi @Karlygash

This could be done using just the String Manipulation node

Please add the following instruction in a String Manipulation node and directly you will get the value in Double format

toDouble( replace( replaceChars( upperCase($date&time diff$), "M ", “”), “H”, “.”))

All the best,

Ael

[Karlygash]

oh, sure))) I was looking for duration to digits node:DDD
Cool answer))
I have added substring , cuz it has such format
toDouble(substr(replace(replaceChars(upperCase($date&time diff$), “M”,"" ), “H” , “.”) , 2 ))

[aworker]

Cool @Karlygash and thanks for validating the answer !

Cheers

Ael

[morpheus]

Hi,
the other option is to set the Duration to Granularity and seconds and divide the results with 3600.

BR

[aworker]

Thanks @morpheus ! Indeed this is a more straight solution that I would recommend in priority.

Best

Ael

[takbb]

Hi @morpheus, you are right but I believe what isn’t totally obvious from the subject is that the workflow already had a Duration to Number node that can return the Duration as purely decimal hours.

I believe (but I may be wrong! ) that what is wanted here is the same duration to be converted from 2H 15m to “2.15” [h.mm] rather than 2.25 hours, so if that is the case, then taking your suggestion further, the duration returned in seconds could be turned into that format using String Manipulation node using the following (really fun to write ) formula:

join(string(toInt($date&time diff$/3600))
	,"."
	,padLeft(string(
		toInt((toDouble($date&time diff$)/3600 - toInt($date&time diff$/3600))*60)
		   ),2,"0")
	)

and of course in that case, it would be returned as a String rather than a numeric, as we wouldn’t really want “2.15” to mean 2.15
(or we could return in minutes and use 60 instead of 3600, if we want to save typing )

It would have been nicer to be able to make use of the mod function in Math Formula node, but then I couldn’t return it as a string, so I decided I was going to have to do it the old-fashioned way!)

After more digging, a lower code method was to get the duration as the original duration (rather than seconds) format and then use an Extract Duration Fields node, extracting Hours and Minutes to two new columns. These can then be fed to a String Manipulation with the expression:

join(string($Hours (#1)$),".",padLeft(string($Minutes$),2,"0"))

A pity that there doesn’t appear to be a way to (a) extract both fields to a single column in a specified format, or (b) have it pad minutes with leading zeroes, so that part still requires code, otherwise I would have just thrown it at a column combiner.

[morpheus]

Hi @takbb,
your right that the initial reqeust was to return a value of 3.15.
But based on my experience this value is a good candidate for a wrong interpretation. I recommend returning a digit value of 3.25 or a string value of ‘03:15’ but not a mixture of both.

[takbb]

Hi @morpheus, that’s good advice. I’m easy on the leading zero for the hours (which of course could be accommodated with another padLeft), but I agree that concatenating a “:” instead of a “.” makes the meaning of the data clearer. I just assumed this was a localisation thing.

[Karlygash]

Hello @takbb @morpheus @aworker
Thank you
I ve never known that in string manipulation we can use formulas as well