Interval query using two fields

Elastic Stack Elasticsearch
1

Is there a way to count the number of documents that fall within an interval, based on two fields?

For example, say you had documents with start and end times. Instead of intervaling over one field (like the start time, in this case), I want intervals that count how many documents are completely or partially contained within each time interval, based off of their start and end times.

I understand how to do this for only one interval using ranges, like:

 "query": {
    "bool" : {
      "must" : [
        {
          "range" : {
            "start_time" : {
              "lt" :  "2018-04-12T00:05:00.000Z" (the end time of the interval)
            }
          }
        },
        {
          "range" : {
            "stop_time" : {
              "gt" :  "2018-04-12T00:04:45.000Z" (the start time of the interval)
            }
          }      
        }
        ...
}

This returns the correct values for one sub-interval, but I would ideally like to execute a query and get the correct values for a time range containing multiple intervals. Is this possible?

2

I believe this what date_range data type is made for I believe. See https://www.elastic.co/guide/en/elasticsearch/reference/current/range.html

3

Interesting, I didn't realize there are range data types. I looked into it, but this would still only allow me to get one interval at a time, right? Histogram and range aggregations don't seem to be compatible with range data types, and the range query only works on one range.

4

Histogram and range aggregations don't seem to be compatible with range data types

Sure but you can always index a range and dates separately.

5

Can you elaborate what you mean?

6

Index:

{
  "start": "2018-04-12T00:04:45.000Z",
  "end": "2018-04-12T00:05:00.000Z",
  "range": {
    "from": "2018-04-12T00:04:45.000Z",
    "to": "2018-04-12T00:05:00.000Z"
  }
}
7

But that still doesn't allow me to query over multiple intervals, right? I'd still only get the one interval, which isn't what I want.

8

I don't know if you can use an array of ranges.

@jpountz do you have an idea?

9

We would like to make histogram and range aggregations able to work on range fields (https://github.com/elastic/elasticsearch/issues/23182) but this will take some time. In the meantime, your best option would be to use a filters aggregation I suppose, with one filter per range that you want to count, regardless of whether you run this range with two fields or a single range field.

10

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.