Filtering by Range

[Robert_Naccache]

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.

[jpountz (Adrien Grand)]

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

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"
}
}
}
}
}

[Robert_Naccache]

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] ??

[jpountz (Adrien Grand)]

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?

[Robert_Naccache]

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!