How to get months and years difference between the two dates?

Last Updated on 01/12/2020 by Patryk Bandurski

Some time ago, I was asked to compute the difference between the two dates (Period). Sounds simple, isn’t it? However, I needed this difference presented in days, months, and years. It was not that obvious how to achieve that. Let’ see how I did it.

Overview

We want to see how long given employee is employed at our company. We know his/her employment date and the current date & time. On the graphics below, you can see a sample evaluation. The seniority is expressed with years, months, and days that have passed since employment. As in the example, the seniority is 5 years, 1 month, and 26 days.

Difference

Date substation and period

The first attempt is to do the date subtraction like |2020-11-22| - |2015-09-27|. Below you can see a simple DataWeave script. Its output is the object with seniority property equal to PT45192H

%dw 2.0
output application/json
var seniority = (|2020-11-22| as Date - payload.startDate as Date)
---
{
	seniority: seniority
}

The outcome type is Period. Simply it means that between those two dates passed 45192 hours.

Period, how to read it?

The generic period looks like here P<date>T<time>. The first part can contain at least one of the following:
[n]Y – years like |P1Y| – 1 year
[n]M – months like |P12M| – 12 months
[n]D – days like |P365D| – 365 days
If duration time is present it contains
[n]H – hours like |PT1H| – 1 hour
[n]M – minutes like |PT60M| – 60 minutes
[n]S – seconds like |PT3600S| – 3600 seconds
where [n] – is the number.

Exracting values from Period

As we have a period value, it is time to transform it into days, months, and years with employee seniority. Down below, you can see a simple DataWeave transformation:

%dw 2.0
output application/json
var seniority = (|2020-11-22| - payload.startDate)
---
{
	seniority: {
		years: seniority.years,
		months: seniority.months,
		days: seniority.days
	}
}

However, the result won’t look like the one we expected. Here is what we got:

{
	seniority: {
                "years": null,
                "months": null,
                "days": 1883
	}
}

As you can see, we received only the difference in days. This is not exactly what we expected. Unfortunately computed Period is in hours even though that we have subtracted dates.

Java Period to the resuce

I thought of using java. In one of my articles, I wrote about using Java in DataWeave. So I decided to take a look closely at Period class specification.

We can easily use Period from the java library (java.time package) to better control what we can do. The period class has a static function called between. It accepts two dates.

Period Period.between(Date date1, Date date2);

Below you can see the DataWeave transformation where I imported Period class. In the next line, I have called between function. It returns Period into seniority variable.

%dw 2.0
output application/json
import java!java::time::Period
var seniority = Period::between(payload.startDate, |2020-11-22|)
---
{
	seniority: {
		years: seniority.years,
		months: seniority.months,
		days: seniority.days
	}
}

Down below you can see evaluated seniority

{
  "seniority": {
    "years": 5,
    "months": 1,
    "days": 26
  }

New Periods DataWeave module! Update!

In the newest version of DataWeave 2.3.1, you can find the new Periods module. It contains a function between, that returns Period consisting of the number of years, months, and days between two dates. It is just brilliant! I had an issue with computing this solely using DataWeave, and it turns out to be implemented by MuleSoft in DataWeave. Let’s see how it works.

%dw 2.0
output application/json
import dw::core::Periods
var seniority = Periods::between(|2020-11-22|, payload.startDate)
---
{
    seniority: {
        years: seniority.years,
        months: seniority.months,
        days: seniority.days
    }
}

The outcome is exactly the same as previously. So we have 5 years, 1 month and 26 days. One thing to notice is the order of arguments—the function between first take newest date. Otherwise, you will receive a negative difference.

Summary

The task was plain and simple. Get the difference between dates in years, months, and days. However, during the testing of DataWeave expression, it was not that simple to receive the expected outcome. DataWeave Period that was computed after two dates were subtracted from each other contained just hours. I was not able to extract expected values from this. I thought of the Java Period class that as well allows computing the difference. Hopefully, I could use between function, and from the computed Period, I was able to extract years, months, and days. In the newest DataWeave 2.3.1 we can use the Periods module that contains between function, that can compute the difference, without the need to import any java module.

Do you know, how to achieve this in another way, using DataWeave? Please let me know and share it in the comment down below.

Thanks Mariano de Achaval for the update on new Periods module.

How to get months and years difference between the two dates?

2 thoughts on “How to get months and years difference between the two dates?

  1. Hi, Though what you show works perfect. There is a way to do it 100% in DataWeave

    %dw 2.0
    output application/json
    import dw::core::Periods
    var seniority = Periods::between(now() as Date, |2020-10-22|)

    {
    seniority: {
    years: seniority.years,
    months: seniority.months,
    days: seniority.days
    }
    }

    This module was introduce in the service pack of 2.3.0 . So if you use the latest version of DW in CH or in Studio you will be able to use it

    1. Hi Mariano,
      thanks for sharing this. I will check this today, and update the article accordingly :). I feel very satisfied, I had a problem, and MuleSoft solved it, perfect.
      Cheers

Leave a Reply

Your email address will not be published. Required fields are marked *

Scroll to top