Data360 Analyze: Calculate Difference Between Two Dates

It is often necessary to calculate the difference between two dates, e.g. to determine how long a customer has used a service.

In this example we calculate the difference between the date the customer was created and the date they finish the service. The example data for the users' create dates are shown below:

And the corresponding data for the finish dates are shown below:

The example data flow is as follows:

As the StartDate and FinishDate fields are unicode strings, Modify Field nodes are used to convert them to Date type. The Modify Fields node for the Finish Data Set also changes the field names to have a prefix ("F_") so the field names will not conflict in the downstream Merge node. In this example the First_Name and Last_Name fields from the Finish Data Set will be redundant in the merged data set so they are exluded by the Modify Fields node.

The data sets are joined using the Merge node with the Match Keys property set to use the unique identifiers from each data set.

A Transform node is then used to calculate the difference between the values in the two date fields. 

The output metadata for the difference field is defined in the ConfigureFields script. 

In the ProcessRecords script, when the two Date type values are subtracted this results in a Python timedelta object. The value of the difference in terms of the number of complete days it represents can be extracted from the timedelta object using the .days method. This value is then output.

The results are as shown below:

You can use a Python expression in the Calculate Fields node to also calculate the difference between the two date field values. The default value to be assumed if a date value is Null can be configured using the node's optional properties. Here the default is set to Null (which would generate a calculation error if a value was Null) but you can also specify today's date, the Epoch value or a custom date.

If you wanted to calculate the number of working days between the two dates you can use the Calculate Workdays node.

Which generate result in the following results.

See the Help documentation for the Calculate Workdays node for further details.

For further information on manipulating date, time and datetime values see the examples in the Help documentation or search for 'timedelta' in the Community Forum.

The example data flow is attached below.