# Range query for dates with IN clause

**URL:** <https://discuss.elastic.co/t/range-query-for-dates-with-in-clause/191897>\
**Category:** Elasticsearch\
**Created:** [July 23, 2019, 6:50pm UTC](https://discuss.elastic.co/t/range-query-for-dates-with-in-clause/191897 "2019-07-23T18:50:33Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![kasi](https://avatars.discourse-cdn.com/v4/letter/k/e19b73/32.png) [@kasi](https://discuss.elastic.co/u/kasi)\
**Post date:** [July 23, 2019, 6:50pm UTC](https://discuss.elastic.co/t/range-query-for-dates-with-in-clause/191897/1 "2019-07-23T18:50:34Z")

</div>

How to query for a range of dates using the IN clause in Elastic Search? I am using Java Rest services.

I want to be able to query for timestamp for a range of dates? like timestamp in (list of dates)

Thanks

---

<div class="post-metadata">

**Author:** ![Ignacio\_Vera](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ignacio_vera/32/36674_2.png) [@Ignacio\_Vera](https://discuss.elastic.co/u/Ignacio_Vera)\
**Post date:** [July 24, 2019, 9:46am UTC](https://discuss.elastic.co/t/range-query-for-dates-with-in-clause/191897/2 "2019-07-24T09:46:31Z")

</div>

I think the only way currently to do that is by creating a boolean query and add each range query as an OR clause.

---

<div class="post-metadata">

**Author:** ![kasi](https://avatars.discourse-cdn.com/v4/letter/k/e19b73/32.png) [@kasi](https://discuss.elastic.co/u/kasi)\
**Post date:** [July 24, 2019, 4:38pm UTC](https://discuss.elastic.co/t/range-query-for-dates-with-in-clause/191897/3 "2019-07-24T16:38:24Z")

</div>

Thanks a lot, I do have other filters also, I have given the sample code below, I need to add the  
filter for the range of dates also. Can you please guide? I tried adding should clause to it and it  
fails

Thanks,

Kasi

BoolQueryBuilder boolQuery=QueryBuilders.boolQuery()  
.must(QueryBuilders.termQuery(DP\_FIELDS\_FILTER\_BY, filterBy))  
.must(QueryBuilders.termQuery(DP\_FIELDS\_GROUP\_BY, groupBy))  
.must(QueryBuilders.termQuery(DP\_FIELDS\_OPERATIONS, operations));

---

<div class="post-metadata">

**Author:** ![Ignacio\_Vera](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ignacio_vera/32/36674_2.png) [@Ignacio\_Vera](https://discuss.elastic.co/u/Ignacio_Vera)\
**Post date:** [July 24, 2019, 6:00pm UTC](https://discuss.elastic.co/t/range-query-for-dates-with-in-clause/191897/4 "2019-07-24T18:00:46Z")

</div>

Not sure if I have understood but adding a new boolean clause as part of your filters containing your range conditions might work:

BoolQueryBuilder boolQuery=QueryBuilders.boolQuery()  
.must(QueryBuilders.termQuery(DP\_FIELDS\_FILTER\_BY, filterBy))  
.must(QueryBuilders.termQuery(DP\_FIELDS\_GROUP\_BY, groupBy))  
.must(QueryBuilders.termQuery(DP\_FIELDS\_OPERATIONS, operations));  
BoolQueryBuilder rangeboolQuery=QueryBuilders.boolQuery();  
// build or range query  
.must(rangeboolQuery));

---

<div class="post-metadata">

**Author:** ![kasi](https://avatars.discourse-cdn.com/v4/letter/k/e19b73/32.png) [@kasi](https://discuss.elastic.co/u/kasi)\
**Post date:** [July 25, 2019, 5:21pm UTC](https://discuss.elastic.co/t/range-query-for-dates-with-in-clause/191897/5 "2019-07-25T17:21:53Z")

</div>

Thanks a lot, will give it a try

---

<div class="post-metadata">

**Author:** ![kasi](https://avatars.discourse-cdn.com/v4/letter/k/e19b73/32.png) [@kasi](https://discuss.elastic.co/u/kasi)\
**Post date:** [July 25, 2019, 9:38pm UTC](https://discuss.elastic.co/t/range-query-for-dates-with-in-clause/191897/6 "2019-07-25T21:38:01Z")

</div>

I tried it, does not work, have given below the JSON version of the query, this is what i added

for (LocalDateTime date:daysOfWeek) {

```
        LocalDateTime from = date.minusMinutes(15);
        LocalDateTime to = date.plusMinutes(15);
        boolQuery1.should(QueryBuilders.rangeQuery(DP_FIELDS_CALCULATED).from(Timestamp.valueOf(from).getTime()).to(Timestamp.valueOf(to).getTime()));
    }

    boolQuery.must(boolQuery1);
```

---

<div class="post-metadata">

**Author:** ![kasi](https://avatars.discourse-cdn.com/v4/letter/k/e19b73/32.png) [@kasi](https://discuss.elastic.co/u/kasi)\
**Post date:** [July 25, 2019, 9:56pm UTC](https://discuss.elastic.co/t/range-query-for-dates-with-in-clause/191897/7 "2019-07-25T21:56:14Z")

</div>

I verified data exists for the timestamp range in kibana

---

<div class="post-metadata">

**Author:** ![Ignacio\_Vera](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ignacio_vera/32/36674_2.png) [@Ignacio\_Vera](https://discuss.elastic.co/u/Ignacio_Vera)\
**Post date:** [July 26, 2019, 12:33pm UTC](https://discuss.elastic.co/t/range-query-for-dates-with-in-clause/191897/8 "2019-07-26T12:33:46Z")

</div>

You will need to provide a reproducible example. I just tried myself and it works for me.

---

<div class="post-metadata">

**Author:** ![kasi](https://avatars.discourse-cdn.com/v4/letter/k/e19b73/32.png) [@kasi](https://discuss.elastic.co/u/kasi)\
**Post date:** [July 26, 2019, 5:06pm UTC](https://discuss.elastic.co/t/range-query-for-dates-with-in-clause/191897/9 "2019-07-26T17:06:25Z")

</div>

Can you please paste the query that you used as a reference?

Thanks,

Kasi

---

<div class="post-metadata">

**Author:** ![kasi](https://avatars.discourse-cdn.com/v4/letter/k/e19b73/32.png) [@kasi](https://discuss.elastic.co/u/kasi)\
**Post date:** [July 28, 2019, 2:17am UTC](https://discuss.elastic.co/t/range-query-for-dates-with-in-clause/191897/10 "2019-07-28T02:17:33Z")

</div>

Probably will be moving to vertica from Elastic Search ..

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [August 25, 2019, 2:22am UTC](https://discuss.elastic.co/t/range-query-for-dates-with-in-clause/191897/11 "2019-08-25T02:22:24Z")

</div>

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