ElasticSearch - difference between two date fields

Question

I have an index in ElasticSearch with two fields of date type (metricsTime & arrivalTime). A sample document is quoted below. In Kibana, I created a scripted field delay for the difference between those two fields. My painless script is:

doc['arrivalTime'].value - doc['metricsTime'].value

However, I got the following error message when navigating to Kibana's Discover tab: class_cast_exception: Cannot apply [-] operation to types [org.joda.time.MutableDateTime] and [org.joda.time.MutableDateTime]. This looks same as the error mentioned in https://discuss.elastic.co/t/problem-in-difference-between-two-dates/121655. But the answer in that page suggests that my script is correct. Could you please help?

Thanks!

{
  "_index": "events",
  "_type": "_doc",
  "_id": "HLV274_1537682400000",
  "_version": 1,
  "_score": null,
  "_source": {
    "metricsTime": 1537682400000,
    "box": "HLV274",
    "arrivalTime": 1539930920347
  },
  "fields": {
    "metricsTime": [
      "2018-09-23T06:00:00.000Z"
    ],
    "arrivalTime": [
      "2018-10-19T06:35:20.347Z"
    ]
  },
  "sort": [
    1539930920347
  ]
}

Answer 1

Check the list of Lucene Expressions to check what expressions are available for date field and how you could use them

Just for sake of simplicity, check the below query. I have created two fields metricsTime and arrivalTime in a sample index I've created.

Sample Document

POST mydateindex/mydocs/1
{
  "metricsTime": "2018-09-23T06:00:00.000Z",
  "arrivalTime": "2018-10-19T06:35:20.347Z"
}

Query using painless script

POST mydateindex/_search
{ "query": {
    "bool": { 
      "must": {
        "match_all": {

        }
      },
        "filter": {
          "bool" : {
            "must" : {
                "script" : {
                    "script" : {
                        "inline" : "doc['arrivalTime'].date.dayOfYear - doc['metricsTime'].date.dayOfYear > params.difference",
                        "lang"   : "painless",
                        "params": {
                          "difference": 2
                        }
                    }
                }
            }
        }
        }

    }
  }
}

Note the below line in the query

"inline" : "doc['arrivalTime'].date.dayOfYear - doc['metricsTime'].date.dayOfYear > params.difference"

Now if you change the value of difference from 2 to 26 (which is one more than the difference in the dates) then you see that the above query would not return the document.

But nevertheless, I have mentioned the query as an example as how using scripting you can compare two different and please do refer to the link I've shared.