Elastic search date_histogram extended_bounds

Question

I want to get date_histogram during specific period, how to restrict the date period? Should I use the extended_bounds parameter? For example : I want to query the date_histogram between '2016-08-01' and '2016-08-31', and the interval is day. I query with this expression :

{
  "aggs": {
    "cf_loan": {
      "date_histogram": {
        "field": "createDate",
        "interval": "day",
        "format": "yyyy-MM-dd",
        "min_doc_count": 0,
        "extended_bounds": {
          "min": "2016-08-01",
          "max": "2016-08-31"
        }
      }
    }
  }
}

But I get the date_histogram not in the range.

score 11 · Answer 1 · answered Sep 05 '16 at 03:54

You're almost there, you need to add a range query in order to only select documents whose createDate field is in the desired range.

{
  "query": {
    "range": {                           <---- add this range query
      "createDate": {
        "gte": "2016-08-01T00:00:00.000Z",
        "lt": "2016-09-01T00:00:00.000Z"
      }
    }
  },
  "aggs": {
    "cf_loan": {
      "date_histogram": {
        "field": "createDate",
        "interval": "day",
        "format": "yyyy-MM-dd",
        "min_doc_count": 0,
        "extended_bounds": {
          "min": "2016-08-01",
          "max": "2016-08-31"
        }
      }
    }
  }
}

The role of the extended_bounds parameter is to make sure you'll get daily buckets from min to max even if there are no documents in them. For instance, say you have 1 document each day between 2016-08-04 and 2016-08-28, then without the extended_bounds parameter, you'd get 25 buckets (2016-08-04, 2016-08-05, 2016-08-06, ..., 2016-08-28).

With the extended_bounds parameter, you'll also get the following buckets but with 0 documents:

2016-08-01
2016-08-02
2016-08-03
2016-08-29
2016-08-30
2016-08-31

Can someone give some guidance on how to do date_histogram with extended bounds in sql? — Harshit Nagar, Apr 15 '21 at 20:59

Elastic search date_histogram extended_bounds

1 Answers1