Date histogram aggregation unexpected result with calendar_interval and offset >= 30 days

[xiaofeiXM]

Elasticsearch Version

7.17

Installed Plugins

No response

Java Version

11

OS Version

x86_64

Problem Description

Based on this doc, 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.

Steps to Reproduce

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:

{
    "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):

"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

{
    "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:

"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
        }
      ]
    }
  }

Logs (if relevant)

No response

[elasticsearchmachine]

Pinging @elastic/es-analytics-geo (Team:Analytics)

[craigtaverner]

I believe this is the expected behaviour, and is as a result of the months having different numbers of days. In fact we can see this same issue occurring when we use the calendar_interval of months. With the same data used above, if we query for normal calendar months we get the expected count of 1 document per month for the eight months from January to August.

GET /test01/_search
{
    "size": 0,
    "aggs": {
        "ShiftedQuarter": {
            "date_histogram": {
                "calendar_interval": "month",
                "min_doc_count": 1,
                "time_zone": "UTC",
                "offset": "+0d",
                "field": "date",
                "format": "yyyy-MM-dd"
            }
        }
    }
}

Results:

"buckets": [
        {
          "key_as_string": "2022-01-01",
          "key": 1640995200000,
          "doc_count": 1
        },
        {
          "key_as_string": "2022-02-01",
          "key": 1643673600000,
          "doc_count": 1
        },
        {
          "key_as_string": "2022-03-01",
          "key": 1646092800000,
          "doc_count": 1
        },
        {
          "key_as_string": "2022-04-01",
          "key": 1648771200000,
          "doc_count": 1
        },
        {
          "key_as_string": "2022-05-01",
          "key": 1651363200000,
          "doc_count": 1
        },
        {
          "key_as_string": "2022-06-01",
          "key": 1654041600000,
          "doc_count": 1
        },
        {
          "key_as_string": "2022-07-01",
          "key": 1656633600000,
          "doc_count": 1
        },
        {
          "key_as_string": "2022-08-01",
          "key": 1659312000000,
          "doc_count": 1
        }
      ]

Each bucket is named with the first day of the month. When we add an offset, for example "+1d" we will get buckets named with the second day of the month. We can keep adding days to the offset up to "+19d", and we'll see the same 8 months (Jan->Aug). Then at "+20d" each document will move to a bucket in the previous month, as expected. So far so good. But the fun really happens when we have an offset the size of the smallest month (February). Take a look at the following table, showing the resulting bucket names for each document when performing monthly aggregations with various offsets:

We see the following behaviours for the offsets used:

• 0 to 19 days: the buckets simply increment their day field, but each document is in the same month as before
• +20d, the documents all appear in the previous month. For example, the first document is dated "2022-01-20" and the bucket it appears in is named "2021-12-21" which ranges from "2021-12-21" to "2022-01-20" inclusive, so that is correct.
• 20 to 27 days: the buckets continue to increment their day field.
• +28d: OK, now it gets interesting. Since February has only 28 days, the un-offset bucket that would have started on the first of February now starts on the first of March. I've marked this bucket in red to highlight it.
• This same issue happens with the 30 day months when we reach +30d, now with three buckets marked in red (the original February bucket is joined by April and June, starting at "2022-05-01" and "2022-07-01" respectively).
• As we continue to increase the offset, the bucket definitions will continue to result in apparent mismatches, but remember that in all cases the bucket is defined as the first day of each month, plus the stated number of days (which will result in a different day of the month for larger numbers)
• Look how interesting it gets for even larger offsets, like +50d where document that were 30 days apart (originally dated the 20th of each month) will end up in the same bucket (if that bucket is 31 days wide, due to the original month before the offset).

This issue is also present in any interval that has a non-standard number of days, as seen in the original quarter issue reported above.

My recommendation is that we consider adding a note to the documentation. Right now the section on calendar_interval does not even mention the effects of offset, and later when offset is described, the only examples are how hourly offsets cross midnight, and does not describe tricky cases like months with different numbers of days (or days with different numbers of hours). We can definitely improve on this.

One last comment. When would a user ever want to use offsets large enough to cause this issue? Off the top of my head I can think of a case. Imagine a date system that did not support leap years. Every four years all days would shift by one day relative to our current calendar. If we had, for example, data from two sources, one that used leap years and one that did not, the offsets over time might be possible to analyse using this approach.

[elasticsearchmachine]

Pinging @elastic/es-docs (Team:Docs)

[xiaofeiXM]

thanks a lot @craigtaverner for your detailed explanation! This current behavior is still a bit surprising to me, not sure what would be its use case. For your question "When would a user ever want to use offsets large enough to cause this issue?": so our use case is this: 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 in our original question I needed to use more than 30 days in the offset and expecting the start date is the same for each bucket.

For now we're thinking a work around which is to use date range aggregation instead, but then it's more work on our side as we need to construct all the intervals. It's a pity that we can't use the date histogram aggregation.

[craigtaverner]

Hi @xiaofeiXM , your use case is interesting and I agree that the current behaviour of calendar_interval and offset does not work for your case. I'll be getting a review of my docs update next week, and can use that opportunity to discuss your use case. Perhaps there are other experts out there that can think of a better solution. I cannot think of anything in Elasticsearch right now, other than your date-range solution. A new feature allowing for calendar offsets could work (eg. offset: "+1m+5d", but that would not solve your immediate needs, and also would only be likely to be implemented if it was felt to be general enough for multiple users.