Compare two dates with Jsonata library

Question

I'm using jsonata library to compare the values entered from screen with the values in a wrapper list. Here I'm inputting a Date range from UI and comparing it with a date in my wrapper list by converting both the dates to Unix Epoch timestampt by using DateTime.getTime() method.

e.g. My JSON is:

{
  "lstData": [
    {
      "strFieldName":"ActivityDate",
      "strValue": "2020-06-28"
      "dateTimeStamp": 1594857600000    
    },
    {
      "strFieldName":"ActivityDate",
      "strValue": "2020-07-10",
      "dateTimeStamp": 1595116800000    
    },
    {
      "strFieldName":"ActivityDate",
      "strValue": "2020-07-25",
      "dateTimeStamp": 1595289600000    
    }
  ]
}

And input StartDate and EndDate are 2020-07-20 and 2020-07-30, where StartDate.getTime() is 1595721600000 and EndDate.getTime() is 1596240000000

Jsonata query is: lstData[strFieldName = "ActivityDate" and (dateTimeStamp >= 1595721600000 and dateTimeStamp <= 1596240000000)]

ABOVE QUERY DOES NOT WORK. I tried many other way to do date comparisons using jsonata, but nothing works. If I remove the date comparison above, then the query works i.e. lstData[strFieldName = "ActivityDate"] works. But there is some error in the date comparison which I'm trying to find.

Can somebody please help me with the right syntax for dates in jsonata?

Thanks, Shruti

score 0 · Answer 1 · answered Aug 10 '20 at 09:01

As far as I can see, there's nothing wrong with your query, it's just that none of those timestamps in millis fall between that range. I'd double check how you calculate the millisecond values because it looks like they don't equate to the string values. E.g. the first one: new Date(1594857600000) evaluates to Jul 16 2020 when I try it.

You could use JSONata to parse the string format if that helps. E.g. lstData.strValue.$toMillis($, '[Y]-[M]-[D]')

Compare two dates with Jsonata library

1 Answers1