# Date\_histogram buckets not as expected

**URL:** <https://discuss.elastic.co/t/date-histogram-buckets-not-as-expected/76864>\
**Category:** Elasticsearch\
**Created:** [February 28, 2017, 8:28pm UTC](https://discuss.elastic.co/t/date-histogram-buckets-not-as-expected/76864 "2017-02-28T20:28:12Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![TanvirSidhu](https://avatars.discourse-cdn.com/v4/letter/t/bbe5ce/32.png) [@TanvirSidhu](https://discuss.elastic.co/u/TanvirSidhu)\
**Post date:** [February 28, 2017, 8:28pm UTC](https://discuss.elastic.co/t/date-histogram-buckets-not-as-expected/76864/1 "2017-02-28T20:28:12Z")

</div>

So, I have a query for a date\_histogram for which, if I set the interval to 1d, the response is:  
"aggregations": {  
"received": {  
"buckets": [  
{  
"key\_as\_string": "2017-01-11T00:00:00.000Z",  
"key": 1484092800000,  
"doc\_count": 2  
},  
{  
"key\_as\_string": "2017-01-12T00:00:00.000Z",  
"key": 1484179200000,  
"doc\_count": 1  
},  
{  
"key\_as\_string": "2017-01-13T00:00:00.000Z",  
"key": 1484265600000,  
"doc\_count": 2  
}  
]  
}  
}

The query for the above result is:

```
{
    "size": 0,
  "query": {
    "bool": {
      "must": [
        <!----Omitted sensitive data---->
        {
          "range": {
            "received": {
              "gte": "1483228800000",
              "lte": "1485907140000"
            }
          }
        }
      ]
    }
  },
  "aggs": {
    "received": {
      "date_histogram": {
        "field": "received",
        "interval": "1d"
      }
    }
  }
}

```

The aggregations are  
Problem is when I change the "interval" to "7d" the result is:  
"aggregations": {  
"received": {  
"buckets": [  
{  
"key\_as\_string": "2017-01-05T00:00:00.000Z",  
"key": 1483574400000,  
"doc\_count": 2  
},  
{  
"key\_as\_string": "2017-01-12T00:00:00.000Z",  
"key": 1484179200000,  
"doc\_count": 3  
}  
]  
}  
}

I expected the result to be the following since the search from Jan 1 to Jan 31 2017 and I want it to bucket for 7 days. So it should bucket Jan 1-Jan 7, Jan 8-Jan 14, Jan 15-Jan 21, ...:  
"aggregations": {  
"received": {  
"buckets": [  
{  
"key\_as\_string": "2017-01-08T00:00:00.000Z",  
"key": 1512691200000,  
"doc\_count": 5  
}  
]  
}  
}

Can someone explain me why that isn't the case and what is wrong with my understanding?

---

<div class="post-metadata">

**Author:** ![TanvirSidhu](https://avatars.discourse-cdn.com/v4/letter/t/bbe5ce/32.png) [@TanvirSidhu](https://discuss.elastic.co/u/TanvirSidhu)\
**Post date:** [March 2, 2017, 5:31pm UTC](https://discuss.elastic.co/t/date-histogram-buckets-not-as-expected/76864/2 "2017-03-02T17:31:24Z")

</div>

Anyone has a clue as to why the buckets are working like this?

---

<div class="post-metadata">

**Author:** ![cbuescher](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/cbuescher/32/60402_2.png) [@cbuescher](https://discuss.elastic.co/u/cbuescher)\
**Post date:** [March 2, 2017, 5:39pm UTC](https://discuss.elastic.co/t/date-histogram-buckets-not-as-expected/76864/3 "2017-03-02T17:39:27Z")

</div>

Hi,  
if you use 7d intervals, they cannot sum up to a whole month and still return only buckets with alwasy 7d length. You'd end up with a bucket at the end of the month with less days in it. The way intervals work, they don't take into accout month/year boundaries.

---

<div class="post-metadata">

**Author:** ![TanvirSidhu](https://avatars.discourse-cdn.com/v4/letter/t/bbe5ce/32.png) [@TanvirSidhu](https://discuss.elastic.co/u/TanvirSidhu)\
**Post date:** [March 2, 2017, 5:48pm UTC](https://discuss.elastic.co/t/date-histogram-buckets-not-as-expected/76864/4 "2017-03-02T17:48:05Z")

</div>

Hi Christoph,

Thanks for the reply. My problem isn't with the last bucket, but with the first one. I expected the first bucket to be Jan 1-Jan 7 and second bucket to be Jan 8-Jan 15, since the search date for my result is Jan 1-Jan30.

Data exists for following dates:  
Jan 11: 2 records  
Jan 12: 1 record  
Jan 13: 2 records

Hence, I expected the result to be:  
Jan 8: doc\_count: 5

But, it does:  
Jan 5: doc\_count: 2  
Jan 12: doc\_count: 3

---

<div class="post-metadata">

**Author:** ![TanvirSidhu](https://avatars.discourse-cdn.com/v4/letter/t/bbe5ce/32.png) [@TanvirSidhu](https://discuss.elastic.co/u/TanvirSidhu)\
**Post date:** [March 2, 2017, 5:50pm UTC](https://discuss.elastic.co/t/date-histogram-buckets-not-as-expected/76864/5 "2017-03-02T17:50:26Z")

</div>

It almost seems like it looks for the first record and then subtracts 7 out of it to create the first bucket and after that it just moves with 7 day increments.

---

<div class="post-metadata">

**Author:** ![cbuescher](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/cbuescher/32/60402_2.png) [@cbuescher](https://discuss.elastic.co/u/cbuescher)\
**Post date:** [March 2, 2017, 6:09pm UTC](https://discuss.elastic.co/t/date-histogram-buckets-not-as-expected/76864/6 "2017-03-02T18:09:02Z")

</div>

> [@TanvirSidhu](#):
>
> It almost seems like it looks for the first record and then subtracts 7 out of it to create the first bucket and after that it just moves with 7 day increments.

That might be coincidence. The fact remains that intervals are agnostic to month/year start etc... Have you tried weeks ('1w') instead? That might take into account calendars.

---

<div class="post-metadata">

**Author:** ![TanvirSidhu](https://avatars.discourse-cdn.com/v4/letter/t/bbe5ce/32.png) [@TanvirSidhu](https://discuss.elastic.co/u/TanvirSidhu)\
**Post date:** [March 2, 2017, 6:11pm UTC](https://discuss.elastic.co/t/date-histogram-buckets-not-as-expected/76864/7 "2017-03-02T18:11:05Z")

</div>

I will try that out and also do some testing as to what happens if I add data prior to Jan 11. I will update the thread with my findings. Thanks for insight Christoph.

---

<div class="post-metadata">

**Author:** ![cbuescher](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/cbuescher/32/60402_2.png) [@cbuescher](https://discuss.elastic.co/u/cbuescher)\
**Post date:** [March 2, 2017, 6:20pm UTC](https://discuss.elastic.co/t/date-histogram-buckets-not-as-expected/76864/8 "2017-03-02T18:20:18Z")

</div>

I tried `1w`, you will get buckets starting in the respective calendar weeks on monday:

```auto
  "hits": {
    "total": 2,
    "max_score": 1,
    "hits": [
      {
        "_index": "test",
        "_type": "test",
        "_id": "2",
        "_score": 1,
        "_source": {
          "date": "2017-03-06"
        }
      },
      {
        "_index": "test",
        "_type": "test",
        "_id": "1",
        "_score": 1,
        "_source": {
          "date": "2017-01-05"
        }
      }
    ]
  },
"aggregations": {
    "received": {
      "buckets": [
        {
          "key_as_string": "2017-01-02T00:00:00.000Z",
          "key": 1483315200000,
          "doc_count": 1
        },
        {
          "key_as_string": "2017-01-09T00:00:00.000Z",
          "key": 1483920000000,
          "doc_count": 0
        },
        {
          "key_as_string": "2017-01-16T00:00:00.000Z",
          "key": 1484524800000,
          "doc_count": 0
        },
        {
          "key_as_string": "2017-01-23T00:00:00.000Z",
          "key": 1485129600000,
          "doc_count": 0
        },
        {
          "key_as_string": "2017-01-30T00:00:00.000Z",
          "key": 1485734400000,
          "doc_count": 0
        },
        {
          "key_as_string": "2017-02-06T00:00:00.000Z",
          "key": 1486339200000,
          "doc_count": 0
        },
        {
          "key_as_string": "2017-02-13T00:00:00.000Z",
          "key": 1486944000000,
          "doc_count": 0
        },
        {
          "key_as_string": "2017-02-20T00:00:00.000Z",
          "key": 1487548800000,
          "doc_count": 0
        },
        {
          "key_as_string": "2017-02-27T00:00:00.000Z",
          "key": 1488153600000,
          "doc_count": 0
        },
        {
          "key_as_string": "2017-03-06T00:00:00.000Z",
          "key": 1488758400000,
          "doc_count": 1
        }
      ]
    }
  }

```

---

<div class="post-metadata">

**Author:** ![TanvirSidhu](https://avatars.discourse-cdn.com/v4/letter/t/bbe5ce/32.png) [@TanvirSidhu](https://discuss.elastic.co/u/TanvirSidhu)\
**Post date:** [March 2, 2017, 6:24pm UTC](https://discuss.elastic.co/t/date-histogram-buckets-not-as-expected/76864/9 "2017-03-02T18:24:33Z")

</div>

My requirement is to bucket it in weeks (7 days) starting from the beginning date of my search. So if the user searches Jan 1-Jan 30, it should bucket jan 1-Jan7, Jan 8-Jan 15,...Any idea how we can do that?

---

<div class="post-metadata">

**Author:** ![TanvirSidhu](https://avatars.discourse-cdn.com/v4/letter/t/bbe5ce/32.png) [@TanvirSidhu](https://discuss.elastic.co/u/TanvirSidhu)\
**Post date:** [March 2, 2017, 7:02pm UTC](https://discuss.elastic.co/t/date-histogram-buckets-not-as-expected/76864/10 "2017-03-02T19:02:09Z")

</div>

Did some more testing and seems like if I use "7d" interval it is bucketing it from Thursday to following Wednesday. Not sure if that's configurable or what's causing it. For now I guess "1w" is a better interval to work with.

---

<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:** [March 30, 2017, 7:02pm UTC](https://discuss.elastic.co/t/date-histogram-buckets-not-as-expected/76864/11 "2017-03-30T19:02:27Z")

</div>

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