Multiple Date Time Difference Calculations

[fostc80857]

Hi

I wish to calculate the time difference (in minutes) between four separate sets of date time fields and create a new column for each. Currently I am doing this using four separate Date & Time Difference nodes but was wondering if this could be done using a single node (e.g., column expression)?

As an example, I am trying to calculate the following:

DateTime4 less DateTime1 = Time (minutes)
DateTime5 less DateTime2 = Time (minutes)
DateTime6 less DateTime3 = Time (minutes)
DateTime7 less DateTime4 = Time (minutes)

Thanks
Chris

[Daniel_Weikert]

You could first split your table then append it and then using 1 datetime difference node
br

[fostc80857]

Hi @Daniel_Weikert

My apologies, I am not sure I understand what you mean.

[lelloba]

Hello @fostc80857 ,

not sure if I understood the task, but let me give a try.

You have 4 datasets containing dateTimes. You need to calculate time difference between dates (I suppose all combinations).

header example: | idDateTime | dateTime |

I’d concatenate all tables into one with a concatenate node, then use a cross joiner node to compute all combinations of dateTimes.
Then use a date time difference node to make the subtraction.

Hope it helps. If not, please upload a small workflow, so we can better help you.

Have a nice day,
Raffaello

[fostc80857]

Hi @lelloba

Apologies if I was not clear above. I was using a generic description rather than providing exact detail. I need to do 4 calculations based on 5 date fields that are already in the same file. These are:

Warned
Planned Start
Planned End
Actual Start
Actual End

I need to calculate the date & time difference in minutes for the following:

Planned Start to Planned End = Planned Duration
Warned to Planned Start = Notice Given
Planned End to Actual End = Overrun
Actual Start to Planned Start = Early Start

I am currently using separate date&time difference nodes for each but was wondering if it could be done with one node rather than 4.

Hope this is a bit clearer.
Thanks
Chris

[lelloba]

Ok, now it’s super clear

I was wondering if you could use a multiple-column Math Formulas node, but it seems not able to handle dates.
With a column expression node you don’t have the formula to compute a time difference.

I think your solution is the easiest one working. Can I ask you why you need to change it?

Raffaello

[fostc80857]

I was just trying to see if I could make the workflow more efficient by using 1 node instead of 4.

[Geo]

Hi fostc80857

If your workflow had many more columns, I guess you would have point against “hardcoding” your solution and come up with something more generic. However, at this point, I think you’re better off with 4 nodes. Anything more generic is likely to involve a more complex workflow with flow variables and either a Loop or Unpivot/Pivot.

Geo