Date histogram doesn't give proper data

Elastic Stack Elasticsearch
1

Hi,

I have used date histogram with 5m interval starting for the last 10 minutes as below:

There is no data before 17:36:45 in my index.

GET metricbeat-7.3.2/_search
{
  "size": 0, 
  "query": {
    "bool": {
      "filter": {
        "range": {
          "@timestamp": {
            "gte": "2019-10-03T17:36:00.000Z",
            "lte": "2019-10-03T17:46:00.000Z"
          }
        }
      }
    }
  },
  "aggs": {
    "histo": {
      "date_histogram": {
        "field": "@timestamp",
        "interval": "5m"
        }
      }
    }
  }

This gives below aggregation output:

"aggregations" : {
    "histo" : {
      "buckets" : [
        {
          "key_as_string" : "2019-10-03T17:35:00.000Z",
          "key" : 1570124100000,
          "doc_count" : 267
        },
        {
          "key_as_string" : "2019-10-03T17:40:00.000Z",
          "key" : 1570124400000,
          "doc_count" : 488
        },
        {
          "key_as_string" : "2019-10-03T17:45:00.000Z",
          "key" : 1570124700000,
          "doc_count" : 70
        }
      ]
    }
  }

But after I make changes in query to fetch data from 17:36:55. I changed the seconds from 00 to 55.

GET metricbeat-7.3.2/_search
{
  "size": 0, 
  "query": {
    "bool": {
      "filter": {
        "range": {
          "@timestamp": {
            "gte": "2019-10-03T17:36:55.000Z",
            "lte": "2019-10-03T17:46:55.000Z"
          }
        }
      }
    }
  },
  "aggs": {
    "histo": {
      "date_histogram": {
        "field": "@timestamp",
        "fixed_interval": "5m"
        }
      }
    }
  }

Then I get a different output as below

 "aggregations" : {
    "histo" : {
      "buckets" : [
        {
          "key_as_string" : "2019-10-03T17:35:00.000Z",
          "key" : 1570124100000,
          "doc_count" : 246
        },
        {
          "key_as_string" : "2019-10-03T17:40:00.000Z",
          "key" : 1570124400000,
          "doc_count" : 488
        },
        {
          "key_as_string" : "2019-10-03T17:45:00.000Z",
          "key" : 1570124700000,
          "doc_count" : 169
        }
      ]
    }
  }

From the above two output, in the first bucket there, document count changes from 267 to 246 because in the second query it starts from 17:36:55 so the second output doesn't take records from 17:36:00 to 17:36:54. Why is this behavior?

I can see from the document that for minute interval, it should start with 00 seconds.If I use this query in watcher then it doesn't take the required documents because of difference in seconds in every watcher execution.

2

Your range query excludes all documents earlier than 2019-10-03T17:36:55.000Z and then the data is put into buckets. So that's why nothing from 17:36:00 to 17:36:54 is coming back in the 2nd query.

What if you express your interval in seconds? That could get around the "bucket begins at 0 seconds" limitation. So something like "fixed_interval": "300s"

3

Hi @psalaets,

I had tried using seconds in fixed_interval but the output is same.

Problem happens when this query is used in watcher. With each watcher execution some milliseconds are added to the next watcher execution and so if a watcher executes with some delay in seconds, then some documents are missed by that watcher execution giving incorrect results.

If initially watcher(triggering at 10 min intervals) execution starts at 17:35:00:30 (17:25:00 till 17:35:00),next at 17:45:00:45 (17:35:00 till 17:45:00) and then the next execution starts at 17:55:01:00 (17:45:01 till 17:55:01) then the documents for that one second will be missed.

If i go with the documentation for date histogram, as show below, then depending on time interval of suppose 5 min, it should also include that documents of 1 seconds because it starts at 00 seconds i.e. 17:45:00 till 17:55:00

date_histogram
date_histogram.PNG964×451 34.6 KB

Is this how date histogram generally works or is it an issue?

Thanks
Nikhil

4

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