Date&Time Difference - Node - For Calculating Duration

[ysari126]

Hi,

I am looking for a duration on two timestamps which should return each value if exist years,months,days,hours,minutes,seconds.

So i have used “Date&Time Difference” node for this purpose but it just returning hours-minutes-seconds. Actually there is sometimes more than 24 hours in returned fields. so it means i am waiting for days(months,years) on that case.

Is there any way to accomplish to get more than hours?

My date fields are timestamp and it looks like:
first value : 2018-09-11T15:01:23
second value: 2018-10-10T17:27:57
desired output: 29 days 2 hours 26 minutes 34 seconds
current knime-node output: 698 hours 26 minutes 34 seconds

Thanks
Yasin

[armingrudd]

Hi @ysari126,

Use this expression in the Column Expressions node after the Date&Time Difference node and let me know if the output is what you want:

hour=regexReplace(column("date&time diff"), "PT(.*)H.*", "$1")
days=toInt(hour/24)
hours=toInt(hour%24)
rep=join(days,"d", hours,"H")
regexReplace(column("date&time diff"), ".*H", rep)

[umutcankurt]

nice solution

[ipazin]

Hi there,

Date&T

@ysari126

Is there any way to accomplish to get more than hours?

Not with Time&Date Difference node. He automatically converts difference to hours when difference between (zoned) date&time columns is calculated. If I may ask is reporting reason why you need it in this form or?

Br,
Ivan

[ysari126]

Hi @ipazin,

Thank you for reply.

I am creating a report which should show duration of two different cases. Something happened for 3 months ago and it has resolved today. So we want to see how long it takes. there are thousands of cases. Actually we can cast date difference to seconds to sort these cases, but it is not readable.

There is an age function in database which returns my output. But on knime i am manipulating data before i am saving data into to excel including time differences of cases. So it would be nice that i was able to do this with Knime.

Thanks

[ysari126]

Hi @armingrudd,

Thank you for your suggestion.

It was also in my mind to write some codes to achieve my output with Column Expression. I thought may be there is an easy way to do that.

[ipazin]

Hi there @ysari126,

I see. Unfortunately currently there is no such age option in KNIME. You can either follow @armingrudd approach, develop your own in Column Expressions node or use multiple Math Formula nodes to achieve this. Good thing is you only need to develop this once, save as a Metanode/Component and reuse it whenever in a need

Br,
Ivan