# Interval query using two fields **URL:** \ **Category:** Elasticsearch\ **Created:** [July 31, 2018, 4:52pm UTC](https://discuss.elastic.co/t/interval-query-using-two-fields/142421 "2018-07-31T16:52:09Z")\ **Posts on this page:** 10\ **Page:** 1
**Author:** ![telastic](https://avatars.discourse-cdn.com/v4/letter/t/ac91a4/32.png) [@telastic](https://discuss.elastic.co/u/telastic)\ **Post date:** [July 31, 2018, 4:52pm UTC](https://discuss.elastic.co/t/interval-query-using-two-fields/142421/1 "2018-07-31T16:52:09Z")
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? ---
**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\ **Post date:** [July 31, 2018, 5:14pm UTC](https://discuss.elastic.co/t/interval-query-using-two-fields/142421/2 "2018-07-31T17:14:56Z")
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](https://www.elastic.co/guide/en/elasticsearch/reference/current/range.html) ---
**Author:** ![telastic](https://avatars.discourse-cdn.com/v4/letter/t/ac91a4/32.png) [@telastic](https://discuss.elastic.co/u/telastic)\ **Post date:** [July 31, 2018, 8:33pm UTC](https://discuss.elastic.co/t/interval-query-using-two-fields/142421/3 "2018-07-31T20:33:42Z")
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. ---
**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\ **Post date:** [July 31, 2018, 9:13pm UTC](https://discuss.elastic.co/t/interval-query-using-two-fields/142421/4 "2018-07-31T21:13:03Z")
> 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. ---
**Author:** ![telastic](https://avatars.discourse-cdn.com/v4/letter/t/ac91a4/32.png) [@telastic](https://discuss.elastic.co/u/telastic)\ **Post date:** [July 31, 2018, 9:20pm UTC](https://discuss.elastic.co/t/interval-query-using-two-fields/142421/5 "2018-07-31T21:20:14Z")
Can you elaborate what you mean? ---
**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\ **Post date:** [July 31, 2018, 9:58pm UTC](https://discuss.elastic.co/t/interval-query-using-two-fields/142421/6 "2018-07-31T21:58:06Z")
Index: ```auto { "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" } } ``` ---
**Author:** ![telastic](https://avatars.discourse-cdn.com/v4/letter/t/ac91a4/32.png) [@telastic](https://discuss.elastic.co/u/telastic)\ **Post date:** [July 31, 2018, 10:01pm UTC](https://discuss.elastic.co/t/interval-query-using-two-fields/142421/7 "2018-07-31T22:01:45Z")
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. ---
**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\ **Post date:** [August 1, 2018, 1:49am UTC](https://discuss.elastic.co/t/interval-query-using-two-fields/142421/8 "2018-08-01T01:49:45Z")
I don't know if you can use an array of ranges. @jpountz do you have an idea? ---
**Author:** ![jpountz](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jpountz/32/45836_2.png) [@jpountz](https://discuss.elastic.co/u/jpountz)\ **Post date:** [August 1, 2018, 7:07am UTC](https://discuss.elastic.co/t/interval-query-using-two-fields/142421/9 "2018-08-01T07:07:53Z")
We would like to make histogram and range aggregations able to work on range fields ([https://github.com/elastic/elasticsearch/issues/23182](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. ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [August 29, 2018, 7:08am UTC](https://discuss.elastic.co/t/interval-query-using-two-fields/142421/10 "2018-08-29T07:08:01Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.