CODEWITHSUNDEEP
elasticsearch
Dhruv Pal
Dhruv Pal

Reputation: 957

Query that works on difference of dates

Consider I have a doc which has createdDate and closedDate. Now I want to find all docs where (closedDate - createdDate) > 2. I am not able to apply script in range field. Any clue how to proceed with this. I think this may be possbile by using scripts. By isn't any way I can perform this by query.

Isn't a way to perform this like

{
    "range" : {
        "date" : {
            "gt" :  "{createdDate} - {closedDate}/d > 2"
        }
    }
}

Upvotes: 1

Views: 46

Answers (2)

Val
Val

Reputation: 217334

Yes, you can do this via script

{
  "query": {
    "bool": {
      "filter": [
        {
          "script": {
            "script": "(doc.closedDate.value - doc.createdDate.value)/86400000 > 2"
          }
        }
      ]
    }
  }
}

Note: make sure to enable dynamic scripting in order to try this.

However, it'd be best to already compute that difference at indexing time and then use a range query on that difference field.

Upvotes: 1

Haphil
Haphil

Reputation: 1250

The only way to do that by query is to index an additonal duration field before-hand into your JSON document. Personally I would store the duration in milliseconds and use filters for queries.

If this is not acceptable you will have to use script fields. Described here and here in the Elasticsearch docu.

IMO saving the durtion to each document is preferable, especially if you frequently use the duration for further analysis. The additional field does not cost a lot of memory, but reduces the need for calculations (and therefore is likly to speed up query time) And Especially in Elasticsearch memory shouldn't be a big issue.

Upvotes: 1

Related Questions