# Date histogram aggregation seems incorrect with calendar\_interval and when offset \>= 30 days **URL:** \ **Category:** Elasticsearch\ **Created:** [January 4, 2023, 10:12pm UTC](https://discuss.elastic.co/t/date-histogram-aggregation-seems-incorrect-with-calendar-interval-and-when-offset-30-days/322492 "2023-01-04T22:12:53Z")\ **Posts on this page:** 5\ **Page:** 1
**Author:** ![xiaofei](https://avatars.discourse-cdn.com/v4/letter/x/b77776/32.png) [@xiaofei](https://discuss.elastic.co/u/xiaofei)\ **Post date:** [January 4, 2023, 10:12pm UTC](https://discuss.elastic.co/t/date-histogram-aggregation-seems-incorrect-with-calendar-interval-and-when-offset-30-days/322492/1 "2023-01-04T22:12:53Z")
Based on [this doc](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-datehistogram-aggregation.html), elasticsearch supports Calendar-aware interval on data histogram aggregation. Specially for a "quarter" interval: > "One quarter is the interval between the start day of the month and time of day and the same day of the month and time of day three months later, so that the day of the month and time of day are the same at the start and end." My understanding of this is every bucket in the response should start at the same day, but that's not the case when I use offset \>= 30 days. Some buckets start at, say 5th of the month, while one other bucket starts at the 6th of the month. Is this a bug or am I missing anything? Elasticsearch Version: 7.17 Steps to reproduce: ```auto PUT /test01 { "mappings": { "properties": { "date": { "type": "date" } } } } POST /test01/_doc { "date": 1642658400000 } // 01/20/2022, 6AM UTC POST /test01/_doc { "date": 1645336800000 } // 02/20/2022, 6AM UTC ... POST /test01/_doc { "date": 1660975200000 } // 08/20/2022, 6AM UTC // basically one document every month from January to August ``` Queries: ```auto { "size": 0, "aggs": { "ShiftedQuarter": { "date_histogram": { "calendar_interval": "quarter", "min_doc_count": 1, "time_zone": "UTC", "offset": "+20d", // offset is less than 30 days "field": "date" } } } } ``` returns below as expected (every bucket starts at the 21st day of the month): ```auto "aggregations": { "ShiftedQuarter": { "buckets": [ { "key_as_string": "2021-10-21T00:00:00.000Z", "key": 1634774400000, "doc_count": 1 }, { "key_as_string": "2022-01-21T00:00:00.000Z", "key": 1642723200000, "doc_count": 3 }, { "key_as_string": "2022-04-21T00:00:00.000Z", "key": 1650499200000, "doc_count": 3 }, { "key_as_string": "2022-07-21T00:00:00.000Z", "key": 1658361600000, "doc_count": 1 } ] } } ``` However if the query use a offset that is large than 30, like ```auto { "size": 0, "aggs": { "ShiftedQuarter": { "date_histogram": { "calendar_interval": "quarter", "min_doc_count": 1, "time_zone": "UTC", "offset": "+35d", "field": "date" } } } } ``` then some bucket starts at the 5th but some starts at the 6th: ```auto "aggregations": { "ShiftedQuarter": { "buckets": [ { "key_as_string": "2021-11-05T00:00:00.000Z", "key": 1636070400000, "doc_count": 1 }, { "key_as_string": "2022-02-05T00:00:00.000Z", "key": 1644019200000, "doc_count": 3 }, { "key_as_string": "2022-05-06T00:00:00.000Z", // 06 as opposed to 05 for other buckets "key": 1651795200000, "doc_count": 3 }, { "key_as_string": "2022-08-05T00:00:00.000Z", "key": 1659657600000, "doc_count": 1 } ] } } ``` ---
**Author:** ![xeraa](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/xeraa/32/48181_2.png) [@xeraa](https://discuss.elastic.co/u/xeraa)\ **Post date:** [January 8, 2023, 11:45pm UTC](https://discuss.elastic.co/t/date-histogram-aggregation-seems-incorrect-with-calendar-interval-and-when-offset-30-days/322492/2 "2023-01-08T23:45:57Z")
Your quote is IMO correct if you work with quarters. But your offset is in days. The quarters start on the 2021-10-01, 2022-01-01, 2022-04-01,... Then you add +35d and depending if the first month has 31 days (like October or January) or 30 days (April), you'll end up on a different day. Not sure this is a feature or a bug. ---
**Author:** ![xiaofei](https://avatars.discourse-cdn.com/v4/letter/x/b77776/32.png) [@xiaofei](https://discuss.elastic.co/u/xiaofei)\ **Post date:** [January 9, 2023, 12:12am UTC](https://discuss.elastic.co/t/date-histogram-aggregation-seems-incorrect-with-calendar-interval-and-when-offset-30-days/322492/3 "2023-01-09T00:12:28Z")
Thanks @xeraa for your response! So our use case is: we have customers whose fiscal quarter could start at, say, the 5th of the second month of a standard calendar quarter, which are 2021-08-05, 2021-11-05, 2022-02-05, 2022-05-05, that's why I need to use more than 30 days in the offset and expecting the start date is the same for each bucket. So IMO this is a bug. Can I submit an issue in gitlab? ---
**Author:** ![xeraa](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/xeraa/32/48181_2.png) [@xeraa](https://discuss.elastic.co/u/xeraa)\ **Post date:** [January 9, 2023, 1:26am UTC](https://discuss.elastic.co/t/date-histogram-aggregation-seems-incorrect-with-calendar-interval-and-when-offset-30-days/322492/4 "2023-01-09T01:26:23Z")
I feel like this is more of a new feature: Quarterly interval from date X. IMO you want to operate on quarters as the offset and not +35d but I also have no idea about the implementation details. But GitHub (not GitLab) should be a good next step. In the meantime, while more manual work with all the intervals, the [date range aggregation](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-daterange-aggregation.html) might get you the right result? ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [February 6, 2023, 1:27am UTC](https://discuss.elastic.co/t/date-histogram-aggregation-seems-incorrect-with-calendar-interval-and-when-offset-30-days/322492/5 "2023-02-06T01:27:19Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.