# Filtering by Range

**URL:** <https://discuss.elastic.co/t/filtering-by-range/113410>\
**Category:** Elasticsearch\
**Created:** [December 28, 2017, 7:35am UTC](https://discuss.elastic.co/t/filtering-by-range/113410 "2017-12-28T07:35:12Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![Robert\_Naccache](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/robert_naccache/32/97656_2.png) [@Robert\_Naccache](https://discuss.elastic.co/u/Robert_Naccache)\
**Post date:** [December 28, 2017, 7:35am UTC](https://discuss.elastic.co/t/filtering-by-range/113410/1 "2017-12-28T07:35:12Z")

</div>

Good Morning and Happy Holidays!

I would like to ask a question, maybe someone can shed some light.  
I'm Pulling data from a database which contains a time stamp.

What i'm trying to do is return all data where the timestamp is the current time -20minutes, but only if its not between 00:00AM and 07:00AM.

What i currently have is (where timestamp is the timestamp field):

> "query": {  
> "bool": {  
> "must":  
> {  
> "match": {  
> "monitoringtable.keyword": "Bananas"  
> }  
> },  
> "filter":  
> {  
> "range": {  
> "timestamp": {  
> "gte": "now-20m"  
> }  
> }  
> },  
> "must\_not": {  
> "range":{  
> "timestamp" : {  
> "gt" : "now/d",  
> "lt" : "now/d+7H"  
> }  
> }  
> }  
> }  
> }

Thing is, it doesn't seem to work. shouldn't now/d+7H mean (now, rounded down to the nearest day + 7 hours)?

i.e. the time now is 9:30AM, if i set it to now/d+9H i still see data i shouldn't be seeing. I also tried a range up and down in case its a timezone thing (I'm UTC - 2) but nothing seems to filter it.

Thank you.

---

<div class="post-metadata">

**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:** [December 28, 2017, 6:43pm UTC](https://discuss.elastic.co/t/filtering-by-range/113410/2 "2017-12-28T18:43:43Z")

</div>

Can you run the following request to see how this query actually gets parsed?

```auto
GET {your_index_name}/_validate/query?rewrite=true
{
"query": {
"bool": {
"must":
{
"match": {
"monitoringtable.keyword": "Bananas"
}
},
"filter":
{
"range": {
"timestamp": {
"gte": "now-20m"
}
}
},
"must_not": {
"range":{
"timestamp" : {
"gt" : "now/d",
"lt" : "now/d+7H"
}
}
}
}
}

```

---

<div class="post-metadata">

**Author:** ![Robert\_Naccache](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/robert_naccache/32/97656_2.png) [@Robert\_Naccache](https://discuss.elastic.co/u/Robert_Naccache)\
**Post date:** [December 29, 2017, 6:50am UTC](https://discuss.elastic.co/t/filtering-by-range/113410/3 "2017-12-29T06:50:39Z")

</div>

Hello Adrein,

Thanks for the reply, i ran the above query and got the following output for:

GET fruits\*/\_validate/query?rewrite=true { ... }

> {  
> "valid": true,  
> "\_shards": {  
> "total": 3,  
> "successful": 3,  
> "failed": 0  
> },  
> "explanations": [  
> {  
> "index": "fruits",  
> "valid": true,  
> "explanation": """MatchNoDocsQuery("User requested "match\_none" query.")"""  
> },  
> {  
> "index": "fruits-2017.12.28",  
> "valid": true,  
> "explanation": """MatchNoDocsQuery("User requested "match\_none" query.")"""  
> },  
> {  
> "index": "fruits-2017.12.29",  
> "valid": true,  
> "explanation": """+monitoringtable.keyword:Bananas -MatchNoDocsQuery("User requested "match\_none" query.") #timestamp:[1514528508629 TO 9223372036854775807]"""  
> }  
> ]  
> }

The first two makes sense cause i don't have any data that matches the 'now-20m' condition in them.  
i checked the epoch time for the timestamp.... weird?

timestamp:[Friday, December 29, 2017 6:21:48.629 AM TO Friday, April 11, 2262 11:47:16.855 PM] ??

---

<div class="post-metadata">

**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:** [December 29, 2017, 8:34am UTC](https://discuss.elastic.co/t/filtering-by-range/113410/4 "2017-12-29T08:34:35Z")

</div>

I suspect it has something to do with the fact that you are using `gt` for the lower bound rather than `gte`, which Elasticsearch understands as: "current day should not match". Does it make things better if you use `gte` and `lte`? If no then can you provide the output of the validate/query API again?

---

<div class="post-metadata">

**Author:** ![Robert\_Naccache](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/robert_naccache/32/97656_2.png) [@Robert\_Naccache](https://discuss.elastic.co/u/Robert_Naccache)\
**Post date:** [December 29, 2017, 8:49am UTC](https://discuss.elastic.co/t/filtering-by-range/113410/5 "2017-12-29T08:49:50Z")

</div>

Hello Adrein,

Thanks for the reply, i think i got it even though it doesn't make sense to me.  
Turns out it was a mix and match of gte and lt for my must\_not case.

What ended up working was:

> "must\_not": {  
> "range":{  
> "timestamp" : {  
> "gte" : "now/d",  
> "lt" : "now/d+7H"  
> }  
> }  
> }

by putting lte it would set the date to tommorow's, not today (not sure why)

After changing it to the above i got:

> "index": "as400-2017.12.29",  
> "valid": true,  
> "explanation": "+monitoringtable.keyword:IB\_CONNUSR -timestamp:[1514505600000 TO 1514530799999] #timestamp:[1514536021005 TO 9223372036854775807]"

Which was what i wanted.

Thank you for taking your time to reply! You've been very helpful!

Happy holidays!

---

<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:** [January 26, 2018, 8:50am UTC](https://discuss.elastic.co/t/filtering-by-range/113410/6 "2018-01-26T08:50:14Z")

</div>

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