Calculating number of days between dates in DataWeave

It can be very useful to be able to calculate the difference between two dates. However, calculating this difference in the amount of days seemed a little bit easier to achieve in a DataWeave transformation in Mule 3 than it actually was.

The default method

We started by implementing the default method to calculate the difference between to dates by just subtracting one from another. This worked fine, however, the result of this subtraction is not a number. The difference between two dates will be represented by a Period object. In most cases this won’t be a problem. However, in our case we explicitly needed to retrieve the amount of days between two dates.

Even though the Period object contains a getDays() method, this would not provide the data we needed. For example, when the Period is P2M7D (2 months and 7 days), the getDays() method just returns 7, so the months will not be translated to days.

Using the getMonths() and getYears() methods and calculating the days by ourselves also isn’t a solution because, of course, month lengths vary and years could include leap years.

Doing it the Java way

So… Back to the drawing board. The next idea was to calculate the difference the same way we could in Java. This means translating dates to (milli)seconds, subtracting and calculating this value back to seconds, minutes, hours and finally days.

In DataWeave :datetime objects can be translated to seconds (so not to milliseconds like Java!) fairly easy, just by casting them to :number objects. Unfortunately, this does not work for :date objects. So we first have to cast a :date to a :datetime (via a :string) object.

When we have both dates represented as seconds, we’re almost done. Just subtract them and we have the difference in seconds. Then it’s a piece of cake to make the calculation from seconds to days.

Example

I have created a DataWeave transformation which demonstrates both of the situations that were explained earlier:

%dw 1.0
%output application/json

// Date values
%var firstDate = "2018-08-24" as :date { format: "yyyy-MM-dd" }
%var secondDate = "2018-10-31" as :date { format: "yyyy-MM-dd" }

// Calculate dates to seconds
%var firstAsNumber = firstDate as :string { format: "yyyy-MM-dd'T00:00:00Z'" } as :datetime as :number
%var secondAsNumber = secondDate as :string { format: "yyyy-MM-dd'T00:00:00Z'" } as :datetime as :number
---
{
     firstDate: firstDate,
     secondDate: secondDate,
     defaultMethod: {
         difference: (firstDate - secondDate),
         differenceDays: (firstDate - secondDate).days
     },
     javaWay: {
         difference: (secondAsNumber - firstAsNumber),
         differenceDays: (secondAsNumber - firstAsNumber) / 60 / 60 / 24
     }
}

The result of the above transformation can be seen below:

{
    "firstDate": "2018-08-24",
    "secondDate": "2018-10-31",
    "defaultMethod": {
        "difference": "P2M7D",
        "differenceDays": 7
    },
    "javaWay": {
        "difference": 5875200,
        "differenceDays": 68
    }
}