2

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
  ]
}
Averell
  • 793
  • 2
  • 10
  • 21

1 Answers1

3

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.

Kamal Kunjapur
  • 8,547
  • 2
  • 22
  • 32
  • Thank you Kamal. doc['arrivalTime'].date.dayOfYear - doc['metricsTime'].date.dayOfYear also returns no error for me. However, I need to get the diffenrence in epoch seconds. If I use dayOfYear, I will get problems when two fields belong to two years (e.g: 31/12/2017 and 01/01/2018). – Averell Oct 19 '18 at 09:55
  • @Averell Also check this link which has more details. https://www.elastic.co/guide/en/elasticsearch/painless/master/painless-api-reference.html – Kamal Kunjapur Oct 19 '18 at 09:56
  • "Date fields are treated as the number of milliseconds since January 1, 1970 and support the Numeric Fields API above, plus access to some date-specific fields:" <<< this is from the link you sent me. They mentioned that Date fields support all numeric function (where .value is first in the list). Is there any way to convert it back to some long number? – Averell Oct 19 '18 at 10:05
  • 3
    @Averell `doc['arrivalTime'].date.getMillis()` Think that is what you are looking for. Let me know if that helps! – Kamal Kunjapur Oct 19 '18 at 10:27
  • Thanks. This work. Don't know why it is not mentioned in the link you sent me. – Averell Oct 19 '18 at 10:32
  • `doc['foo'].date.getMillis()` did not work for me in Elasticsearch 7.5 - errors from `JodaCompatibleZonedDateTime`. Looking into class methods involved, this finally worked: `doc['foo'].getValue().toEpochSeconds()`. Annoyingly, this drops fractions of seconds, since `toEpochSeconds()` returns a `long`, but this isn't much of a concern to me in my current usage. – jor Jan 30 '20 at 00:16