# Date histogram aggregation over duration from startDate to endDate

**URL:** <https://discuss.elastic.co/t/date-histogram-aggregation-over-duration-from-startdate-to-enddate/297944>\
**Category:** Elasticsearch\
**Created:** [February 22, 2022, 7:16pm UTC](https://discuss.elastic.co/t/date-histogram-aggregation-over-duration-from-startdate-to-enddate/297944 "2022-02-22T19:16:53Z")\
**Posts on this page:** 14\
**Page:** 1

<div class="post-metadata">

**Author:** ![truptir](https://avatars.discourse-cdn.com/v4/letter/t/58f4c7/32.png) [@truptir](https://discuss.elastic.co/u/truptir)\
**Post date:** [February 22, 2022, 7:16pm UTC](https://discuss.elastic.co/t/date-histogram-aggregation-over-duration-from-startdate-to-enddate/297944/1 "2022-02-22T19:16:53Z")

</div>

Hi, I m trying to create an elastic query that should return all fields in each bucket using date histogram builder.

I have an index that has thresholdValue with a start date and end date of 2/3 months period. Now I want to retrieve thresholdValue in each month bucket.

elastic index doc:

```auto
{
"type":"GEN"
"startDate":"2022-01-01",
"endDate":"2022-02-28",
"thresholdValue":"1"
},
{
"type":"GEN"
"startDate":"2022-03-01",
"endDate":"2022-05-31",
"thresholdValue":"1.5"
}

```

Query:

```auto
POST /rating/_search
{
  "aggs": {
    "thresholdValue": {
      "date_histogram": {
        "field": "endDate",
        "format": "yyyy-MM-dd",
        "time_zone": "+05:30",
        "calendar_interval": "1M",
        "offset": 0,
        "offset": 0,
        "order": {
          "_key": "asc"
        },
        "keyed": false,
        "min_doc_count": 0,
        "extended_bounds": {
          "min": "2022-01-01",
          "max": "2022-03-31"
        }
      },
      "aggregations": {
        "thresholdValue": {
          "sum": {
            "field": "thresholdValue"
          }
        }
      }
    }
  }
}

```

Result I m getitng is:

```auto
"aggregations" : {
    "thresholdValue" : {
      "buckets" : [
        {
          "key_as_string" : "2022-01-01",
          "key" : 1640975400000,
          "doc_count" : 5,
          "thresholdValue" : {
            "value" : 1.0
          }
        },
        {
          "key_as_string" : "2022-02-01",
          "key" : 1643653800000,
          "doc_count" : 0,
          "thresholdValue" : {
            "value" : 0.0
          }
        },
        {
          "key_as_string" : "2022-03-01",
          "key" : 1646073000000,
          "doc_count" : 0,
          "thresholdValue" : {
            "value" : 1.5
          }
        }
      ]
    }
  }
}

```

My expecting result is 2nd bucket should also have thresholdValue 1.0 and the 4th bucket should have a value 1.5 as it matches under start and end date. but it is considering value in the 1st bucket only.

Thanks:)

---

<div class="post-metadata">

**Author:** ![Tomo\_M](https://avatars.discourse-cdn.com/v4/letter/t/848f3c/32.png) [@Tomo\_M](https://discuss.elastic.co/u/Tomo_M)\
**Post date:** [February 22, 2022, 7:30pm UTC](https://discuss.elastic.co/t/date-histogram-aggregation-over-duration-from-startdate-to-enddate/297944/2 "2022-02-22T19:30:02Z")

</div>

Date histogram buckets include start but not include end.  
As it is histogram, each documents are divided into some single buckets. They shoud not be duplicated.

---

<div class="post-metadata">

**Author:** ![truptir](https://avatars.discourse-cdn.com/v4/letter/t/58f4c7/32.png) [@truptir](https://discuss.elastic.co/u/truptir)\
**Post date:** [February 23, 2022, 4:58am UTC](https://discuss.elastic.co/t/date-histogram-aggregation-over-duration-from-startdate-to-enddate/297944/3 "2022-02-23T04:58:30Z")

</div>

Hi @Tomo_M, Thanks for your quick reply. Is there any alternative to achieve this.

---

<div class="post-metadata">

**Author:** ![casterQ](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/casterq/32/93257_2.png) [@casterQ](https://discuss.elastic.co/u/casterQ)\
**Post date:** [February 23, 2022, 10:18am UTC](https://discuss.elastic.co/t/date-histogram-aggregation-over-duration-from-startdate-to-enddate/297944/4 "2022-02-23T10:18:10Z")

</div>

you can use [Date range](https://www.elastic.co/guide/en/elasticsearch/reference/7.17/search-aggregations-bucket-daterange-aggregation.html) define your own date range

---

<div class="post-metadata">

**Author:** ![casterQ](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/casterq/32/93257_2.png) [@casterQ](https://discuss.elastic.co/u/casterQ)\
**Post date:** [February 23, 2022, 10:21am UTC](https://discuss.elastic.co/t/date-histogram-aggregation-over-duration-from-startdate-to-enddate/297944/5 "2022-02-23T10:21:47Z")

</div>

such as :

```auto
POST test/_search?size=0
{
  "aggs": {
    "range": {
      "date_range": {
        "field": "date",
        "ranges": [
          {
            "from": "2015-07-30T23:33:09",
            "to": "2015-08-30T23:33:09"
          },
          {
            "from": "2015-07-23T23:33:09",
            "to": "2015-08-31T23:33:09"
          },
          {
            "from": "2015-08-02T23:33:09",
            "to": "2015-09-30T23:33:09"
          }
        ]
      }
    }
  }
}

```

---

<div class="post-metadata">

**Author:** ![Tomo\_M](https://avatars.discourse-cdn.com/v4/letter/t/848f3c/32.png) [@Tomo\_M](https://discuss.elastic.co/u/Tomo_M)\
**Post date:** [February 23, 2022, 10:27am UTC](https://discuss.elastic.co/t/date-histogram-aggregation-over-duration-from-startdate-to-enddate/297944/6 "2022-02-23T10:27:25Z")

</div>

> **Date range aggregation**  
> A range aggregation that is dedicated for date values. The main difference between this aggregation and the normal [range](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-range-aggregation.html) aggregation is that the `from` and `to` values can be expressed in [Date Math](https://www.elastic.co/guide/en/elasticsearch/reference/current/common-options.html#date-math) expressions, and it is also possible to specify a date format by which the `from` and `to` response fields will be returned. **Note that this aggregation includes the `from` value and excludes the `to` value for each range.**  
> [Date range aggregation | Elasticsearch Guide [8.11] | Elastic](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-daterange-aggregation.html)

The document says Date range aggregation also excludes `to` value. It is the same as Range aggregation.

I suppose using [Filters aggregation](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-filters-aggregation.html) with [Range query](https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-range-query.html) is better. In range query, you can use `gte` and `lte` to include the limit values.

---

<div class="post-metadata">

**Author:** ![Tomo\_M](https://avatars.discourse-cdn.com/v4/letter/t/848f3c/32.png) [@Tomo\_M](https://discuss.elastic.co/u/Tomo_M)\
**Post date:** [February 23, 2022, 10:29am UTC](https://discuss.elastic.co/t/date-histogram-aggregation-over-duration-from-startdate-to-enddate/297944/7 "2022-02-23T10:29:18Z")

</div>

Or it is possible using something like `{to: 2015-08-30T00:00:00.001}` with Date range aggregation.

---

<div class="post-metadata">

**Author:** ![truptir](https://avatars.discourse-cdn.com/v4/letter/t/58f4c7/32.png) [@truptir](https://discuss.elastic.co/u/truptir)\
**Post date:** [February 23, 2022, 1:46pm UTC](https://discuss.elastic.co/t/date-histogram-aggregation-over-duration-from-startdate-to-enddate/297944/8 "2022-02-23T13:46:37Z")

</div>

I think I made the question complicated. My requirement is not only to include the end date.

I have an index doc that has a threshold data time period-wise.

```auto
{
StartDate: 2022-01-01
endDate: 2022-03-31
Threshold: 1.5
}
{
StartDate: 2022-04-01
endDate: 2022-12-31
Threshold: 2.7
}

```

Now I want to retrieve the threshold in monthly or weekly buckets.

```auto
{
bucket:[
2022-01-01:{
Threshold: 1.5
},
2022-02-01:{
Threshold: 1.5
},
2022-03-01:{
Threshold: 1.5
},
2022-04-01:{
Threshold: 2.7
},
2022-05-01:{
Threshold: 2.7
}
]
}

```

---

<div class="post-metadata">

**Author:** ![Tomo\_M](https://avatars.discourse-cdn.com/v4/letter/t/848f3c/32.png) [@Tomo\_M](https://discuss.elastic.co/u/Tomo_M)\
**Post date:** [February 23, 2022, 2:21pm UTC](https://discuss.elastic.co/t/date-histogram-aggregation-over-duration-from-startdate-to-enddate/297944/9 "2022-02-23T14:21:26Z")

</div>

I got what you want, but it is a bit far from date histogram aggregation on date fields.  
It is not only aggregation but it need flattening or some reconstructing the data.

For such case, [Range field type](https://www.elastic.co/guide/en/elasticsearch/reference/current/range.html) is a possible option.

```auto
PUT /test_date_range/
{
  "mappings": {
    "properties": {
      "dateRange":{
        "type":"date_range"
      },
      "val": {"type":"float"}
    }
  }
}

POST test_date_range/_bulk
{"index":{}}
{"val":1.5,"dateRange":{"gte":"2022-01-01","lte":"2022-03-31"}}
{"index":{}}
{"val":2.7,"dateRange":{"gte":"2022-04-01","lte":"2022-12-31"}}
{"index":{}}
{"val":2.0,"dateRange":{"gte":"2022-01-01","lte":"2022-02-15"}}
{"index":{}}
{"val":2.5,"dateRange":{"gte":"2022-02-15","lte":"2022-12-31"}}

GET test_date_range/_search

GET test_date_range/_search?filter_path=aggregations.date.buckets.key_as_string,aggregations.date.buckets.max.value
{
  "size":0,
  "aggs": {
    "date": {
      "date_histogram": {
        "field": "dateRange",
        "calendar_interval": "month"
      },
      "aggs": {
        "max": {
          "max": {
            "field": "val"
          }
        }
      }
    }
  }
}

```

```auto
{
  "aggregations" : {
    "date" : {
      "buckets" : [
        {
          "key_as_string" : "2022-01-01T00:00:00.000Z",
          "max" : {
            "value" : 2.0
          }
        },
        {
          "key_as_string" : "2022-02-01T00:00:00.000Z",
          "max" : {
            "value" : 2.5
          }
        },
        {
          "key_as_string" : "2022-03-01T00:00:00.000Z",
          "max" : {
            "value" : 2.5
          }
        },
        {
          "key_as_string" : "2022-04-01T00:00:00.000Z",
          "max" : {
            "value" : 2.700000047683716
          }
        },
        {
          "key_as_string" : "2022-05-01T00:00:00.000Z",
          "max" : {
            "value" : 2.700000047683716
          }
        },
        {
          "key_as_string" : "2022-06-01T00:00:00.000Z",
          "max" : {
            "value" : 2.700000047683716
          }
        },
        {
          "key_as_string" : "2022-07-01T00:00:00.000Z",
          "max" : {
            "value" : 2.700000047683716
          }
        },
        {
          "key_as_string" : "2022-08-01T00:00:00.000Z",
          "max" : {
            "value" : 2.700000047683716
          }
        },
        {
          "key_as_string" : "2022-09-01T00:00:00.000Z",
          "max" : {
            "value" : 2.700000047683716
          }
        },
        {
          "key_as_string" : "2022-10-01T00:00:00.000Z",
          "max" : {
            "value" : 2.700000047683716
          }
        },
        {
          "key_as_string" : "2022-11-01T00:00:00.000Z",
          "max" : {
            "value" : 2.700000047683716
          }
        },
        {
          "key_as_string" : "2022-12-01T00:00:00.000Z",
          "max" : {
            "value" : 2.700000047683716
          }
        }
      ]
    }
  }
}

```

Though it is not documented, the relation between date\_range field and date histogram bucket range is limited to "`INTERSECTS`".

Range queries over range fields support `relation` parameter which can be one of `WITHIN` , `CONTAINS` , `INTERSECTS` (default). There is no such parameter in Histogram aggregation over range fields, however. (Maybe it is worth creating feature request in Github repository, if you want.)

(To check this behavior, I added ranges start/end in the middle of month.)

---

<div class="post-metadata">

**Author:** ![truptir](https://avatars.discourse-cdn.com/v4/letter/t/58f4c7/32.png) [@truptir](https://discuss.elastic.co/u/truptir)\
**Post date:** [February 24, 2022, 5:57am UTC](https://discuss.elastic.co/t/date-histogram-aggregation-over-duration-from-startdate-to-enddate/297944/10 "2022-02-24T05:57:59Z")

</div>

Thank you So much @Tomo_M for your help. This solved my problem:)

---

<div class="post-metadata">

**Author:** ![Tomo\_M](https://avatars.discourse-cdn.com/v4/letter/t/848f3c/32.png) [@Tomo\_M](https://discuss.elastic.co/u/Tomo_M)\
**Post date:** [February 24, 2022, 6:30am UTC](https://discuss.elastic.co/t/date-histogram-aggregation-over-duration-from-startdate-to-enddate/297944/11 "2022-02-24T06:30:41Z")

</div>

I'm glad to hear my answer help you. If you satisfied with my answer, please check as Solution. Thanks!

---

<div class="post-metadata">

**Author:** ![celalsur](https://avatars.discourse-cdn.com/v4/letter/c/82dd89/32.png) [@celalsur](https://discuss.elastic.co/u/celalsur)\
**Post date:** [March 4, 2022, 6:40am UTC](https://discuss.elastic.co/t/date-histogram-aggregation-over-duration-from-startdate-to-enddate/297944/12 "2022-03-04T06:40:13Z")

</div>

it also worked for me, thanks for the useful answers.

---

<div class="post-metadata">

**Author:** ![celalsur](https://avatars.discourse-cdn.com/v4/letter/c/82dd89/32.png) [@celalsur](https://discuss.elastic.co/u/celalsur)\
**Post date:** [March 10, 2022, 10:29am UTC](https://discuss.elastic.co/t/date-histogram-aggregation-over-duration-from-startdate-to-enddate/297944/13 "2022-03-10T10:29:34Z")

</div>

actually i also had same issue.[.](https://www.surveyzop.com/wendys-lunch-hours/) [.](https://www.surveyzop.com/)

---

<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:** [April 7, 2022, 10:30am UTC](https://discuss.elastic.co/t/date-histogram-aggregation-over-duration-from-startdate-to-enddate/297944/14 "2022-04-07T10:30:13Z")

</div>

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