Date query does not work

Elastic Stack Kibana
1

Hi i have the following query not not @timestamp: [now/d+1h now/d+2h] but it does not seem to work? what am i missing? (im trying to exclude a specific timespan from the query)

2

you could try with a query like this:

GET _search
{
  "query": {
    "bool": {
      "must_not": [{
         "range": {
            "FIELD": {
              "gte": 10,
              "lte": 20
            }
         }
      }]
    }
  }
}
3

How would i express that in kibana? (im new and only know how to use the syntax you input in the textbox)

4

one way to do it would be:

  • go to DISCOVER
  • expand one record, next to your @timestamp field you will see a "zoom in" icon, which says filter on value (on hover).
  • click that zoom in icon, filter will be added to your query bar.
  • mouse over a filter and click EDIT icon
  • you can now enter your custom filter, something like:
{
  "query": {
    "bool": {
      "must_not": [
        {
          "range": {
            "@timestamp": {
              "gte": "now/d+1h",
              "lte": "now/d+2h"
            }
          }
        }
      ]
    }
  }
}
  • click DONE, your filter is updated to the custom one you entered.

let me know if this helps

5

Thanks i managed to enter the query but it did not work as expected it removes everything after now/d+1h. It should only remove records between now/d+1h and now/d+2h?

just for fun i changed must_not to must, correct me if im wrong but it should show 1h of data? but it shows all data after now/d+1h.

6

yes, it should show you 1h of data.

just to check ... you are trying to filter out all data from today 01:00AM till 02:00AM ?

7

Yes im trying to filter out all data between 01:00 and 02:00 for today (its more specific in reality but if i can get this to work ill figure the rest out).
Must result:
Must not result:

Now im in UNC +1 so it might give a diff on one hour as the query seems to run at UNC+0.

8

i was finally able to make it work with the following filter query:

{
  "query": {
    "bool": {
      "must": [
        {
          "range": {
            "@timestamp": {
              "lte": "now/d+2h",
              "gte": "now/d+25h"
            }
          }
        }
      ]
    }
  },
  "size": 0
}

this seems to be a bug, with lte now/d translates to today at 00:00, however with gte it seems that now/d translates to yesterday at 00:00 (thats why you need to add 25 hours instead of 1 to achieve desired results).

9

hmm if i run this query i get no results at all...

10

are you sure you have data from 01:00 to 02:00 am today ? it works ok for me ....

also it seems its not a bug but its intentional:

https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-range-query.html#ranges-on-dates

try using lte+gt or lt+gte instead of lte+gte to get more consistent results.

11

I do have data but even if i didnt by using the +25h version all data gets removed, ie nothing is matched!!
Filter disabled:

No filter.png778×358 16.9 KB

Filter enabled:
With filter.png778×358 22.3 KB

Also tried to use gt or lt same result no matched data at all..
What does "size": 0 do?

12

This is a bug in how the date round logic is applied for lte. I've opened https://github.com/elastic/elasticsearch/issues/22670

You can work around it by using lt instead, which doesn't apply the rounding.

1 Like
13

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