# Date Histogram doesn't work with calendar\_interval month

**URL:** <https://discuss.elastic.co/t/date-histogram-doesnt-work-with-calendar-interval-month/328848>\
**Category:** Elasticsearch\
**Tags:** transforms\
**Created:** [March 29, 2023, 3:26pm UTC](https://discuss.elastic.co/t/date-histogram-doesnt-work-with-calendar-interval-month/328848 "2023-03-29T15:26:48Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![Tobse](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/tobse/32/119154_2.png) [@Tobse](https://discuss.elastic.co/u/Tobse)\
**Post date:** [March 29, 2023, 3:26pm UTC](https://discuss.elastic.co/t/date-histogram-doesnt-work-with-calendar-interval-month/328848/1 "2023-03-29T15:26:48Z")

</div>

I have tried to use a `group_by` with a `date_histogram` by `month`. Our example works like expected with `"calendar_interval": "day"` but returns nothing with `"calendar_interval": "month"`. In fact it seems wo work with `day`, `week` and `hour` but not for `month`.

### Sample Data

```json
[
  {
    "productID": 1001,
    "accountID": "1",
    "timeStamp": "2023-02-28T07:00:15.043331859Z"
  },
  {
    "productID": 1002,
    "accountID": "1",
    "timeStamp": "2023-02-28T07:00:15.043331859Z"
  },
  {
    "productID": 1001,
    "accountID": "2",
    "timeStamp": "2023-02-28T07:00:15.043331859Z"
  }
] 

```

### Transform

Group by `month` and `accountID`.

```json
{
  "source": {
    "index": "product-tracking-events*"
  },
  "pivot": {
    "group_by": {
      "time": {
        "date_histogram": {
          "field": "timeStamp",
          "calendar_interval": "month"
        }
      },
      "account": {
        "terms": {
          "field": "accountID.keyword"
        }
      }
    },
    "aggregations": {
      "productIDs": {
        "cardinality": {
          "field": "productID",
          "precision_threshold": 1000
        }
      }
    }
  },
  "frequency": "1m",
  "sync": {
    "time": {
      "delay": "60s",
      "field": "timeStamp"
    }
  },
  "description": "Saves number of unique productIDs for an accountID",
  "dest": {
    "index": "transform-product-events"
  }
}

```

### Expected Result

We want to see the number of unique products per account. So account `1` had `2` products and account `2` had `1` product:

```json
[
  {
    "date": "2023-02-01T00:00:00.0Z",
    "account": "1",
    "productIDs": 2
  },
  {
    "date": "2023-02-01T00:00:00.0Z",
    "account": "2",
    "productIDs": 1
  }
]

```

---

<div class="post-metadata">

**Author:** ![Hendrik\_Muhs](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/hendrik_muhs/32/25802_2.png) [@Hendrik\_Muhs](https://discuss.elastic.co/u/Hendrik_Muhs)\
**Post date:** [March 29, 2023, 4:11pm UTC](https://discuss.elastic.co/t/date-histogram-doesnt-work-with-calendar-interval-month/328848/2 "2023-03-29T16:11:39Z")

</div>

Your steps look ok to me and I could not reproduce it.

Could you post how `accountID` is mapped? You specify `accountID.keyword`, I guess your problem has something to do with multi fields.

For debugging you could check what happens if you use ordinary aggs and/or a composite aggregation:

```auto
GET product-tracking-events*/_search
{
  "size": 0,
  "aggs": {
    "c": {
      "composite": {
        "sources": [
          {
            "time": {
              "date_histogram": {
                "field": "timeStamp",
                "calendar_interval": "month"
              }
            }
          },
          {
            "account": {
              "terms": {
                "field": "accountID.keyword"
              }
            }
          }
        ]
      }
    }
  }
}

```

Does the problem persist regarding `day` vs. `month`?

In both _transform preview_ as well as _composite aggs_ you can try what happens if you set `missing_bucket` to `true` like this:

```auto
            "account": {
              "terms": {
                "field": "accountID.keyword",
                "missing_bucket": true
              }
            }

```

---

<div class="post-metadata">

**Author:** ![stephenb](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/stephenb/32/40856_2.png) [@stephenb](https://discuss.elastic.co/u/stephenb)\
**Post date:** [March 29, 2023, 4:18pm UTC](https://discuss.elastic.co/t/date-histogram-doesnt-work-with-calendar-interval-month/328848/3 "2023-03-29T16:18:59Z")

</div>

@Tobse How far back does your Data go?

---

<div class="post-metadata">

**Author:** ![Tobse](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/tobse/32/119154_2.png) [@Tobse](https://discuss.elastic.co/u/Tobse)\
**Post date:** [March 30, 2023, 8:16am UTC](https://discuss.elastic.co/t/date-histogram-doesnt-work-with-calendar-interval-month/328848/4 "2023-03-30T08:16:28Z")

</div>

For testing we used only data from the same day. The timestamps were from 14 pm to 17pm.

---

<div class="post-metadata">

**Author:** ![Tobse](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/tobse/32/119154_2.png) [@Tobse](https://discuss.elastic.co/u/Tobse)\
**Post date:** [March 30, 2023, 8:23am UTC](https://discuss.elastic.co/t/date-histogram-doesnt-work-with-calendar-interval-month/328848/5 "2023-03-30T08:23:03Z")

</div>

> [@Hendrik\_Muhs](#):
>
> garding `day` vs. `month`?

### mapping

The mapping is:

```json
{
  "mappings": {
    "_doc": {
      "properties": {
        "accountID": {
          "type": "text",
          "fields": {
            "keyword": {
              "type": "keyword",
              "ignore_above": 256
            }
          }
        },
        "productID": {
          "type": "long"
        },
        "timeStamp": {
          "type": "date"
        }
      }
    }
  }
}

```

### missing bucket

The field `accountID.keyword` is a mandatory field. We tried it with `"missing_bucket": true` which made no difference.

### composite aggs

When we use a `composite aggs` with `months` or `days` it works like expected. We get the grouped data.

---

<div class="post-metadata">

**Author:** ![Hendrik\_Muhs](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/hendrik_muhs/32/25802_2.png) [@Hendrik\_Muhs](https://discuss.elastic.co/u/Hendrik_Muhs)\
**Post date:** [March 30, 2023, 9:15am UTC](https://discuss.elastic.co/t/date-histogram-doesnt-work-with-calendar-interval-month/328848/6 "2023-03-30T09:15:15Z")

</div>

Thanks @Tobse

what is your version of the stack, so I can try to reproduce it on the same version?

In the example data your dates were all in february. Does that fit with the data you are testing with?

By default a `date_histogram` in transform only creates a bucket if the bucket is _complete_. With other words, a bucket for February gets created, but a bucket for March will not be created today - as we have March 30th - but it will be created the earliest April 1st. The same applies for days, a bucket for today would not be created today, because the day isn't over.

If you want interim buckets, so filling buckets although they are not complete yet, you can set the parameter `align_checkpoints` to `false`, like this:

```auto
"settings": {
  "align_checkpoints": false
}

```

This setting will cause frequent updates to the documents in the transform destination index, of course only if an update is necessary, still this is more expensive. But you can see the monthly results while the month isn't over yet.

---

<div class="post-metadata">

**Author:** ![Tobse](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/tobse/32/119154_2.png) [@Tobse](https://discuss.elastic.co/u/Tobse)\
**Post date:** [March 30, 2023, 10:44am UTC](https://discuss.elastic.co/t/date-histogram-doesnt-work-with-calendar-interval-month/328848/7 "2023-03-30T10:44:00Z")

</div>

We are using ES version **7.17.7** at the moment.

@Hendrik_Muhs Thanks for the hint. Yes, you were right, we missed the `settings.align_checkpoints` flag. We are trying to transform the data while it is generated, because we are not able to store a whole month of data.

Our goal is to aggregate the unique-IDs count for the current month and then automatically roll over to the new month once it begins while beeing able to see the data of the current month.

We included `"settings.align_checkpoints": false` in our current config, but now, after letting it run some time we seem to have the Problem that the buckets seem to be created correctly, but the `productIDs` aggregation is allways `0`

```json
"_source": {
    "productIDs": 0,
    "time": "2023-03-01T00:00:00.000Z",
    "account": "a1"
}

```

Any idea what the Problem here could be?

---

<div class="post-metadata">

**Author:** ![Hendrik\_Muhs](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/hendrik_muhs/32/25802_2.png) [@Hendrik\_Muhs](https://discuss.elastic.co/u/Hendrik_Muhs)\
**Post date:** [March 30, 2023, 12:18pm UTC](https://discuss.elastic.co/t/date-histogram-doesnt-work-with-calendar-interval-month/328848/8 "2023-03-30T12:18:02Z")

</div>

> [@Tobse](#):
>
> We are trying to transform the data while it is generated, because we are not able to store a whole month of data.

That's a problem. When transform re-creates a bucket, it recalculates it. All data necessary for that bucket must still be retrievable. With other words, at least the last month must be there and you can only delete a month once it's fully over.

Your usage of `cardinality` illustrates the problem. While a `min`, `max`, `sum` would be possible to calculate without historic data, `cardinality` requires an approximate data structure called _HyperLogLog++_. Think of it as a large binary array. We don't store this binary array, but only the final output. That means every time the bucket has to be re-calculated, this array gets re-created to calculate the _cardinality_ from it. Technically speaking a `min` could be calculated by taking the min of the current and new value, but because we support _many_ aggregations, including `cardinality` and `scripted_metric`, the continuous mode works by always re-calculating the bucket.

If you don't have the resources to store a full month of data I only see the option to decrease the time window of the `date_histogram`, maybe you can store at least a week.

For cardinality however that's a problem again. If you have 4 documents for 4 weeks and a cardinality value for each week, it is impossible to calculate the cardinality of a month, because you can't tell from 4 numbers, how many of them overlap.

### Possible solution

What I can think of is creating stacked transforms, 2 transforms chained together. transform 1 summarizes data daily with a group\_by on `timestamp`, `accountID` and `productID`. As aggregation you could count the number of docs that match that combination:

```auto
 "count" : { "value_count" : { "field" : "productID" } }

```

My hope is, that this reduces the source data significantly, which only works if you have tons of documents per day, account and product. Note, this transform does _not_ use a `cardinality` agg.

Given that transform you can delete the source data for a day after that day is over.

transform 2 could run on the destination of transform 1 and do your monthly summary. You need to _keep_ the data from the 1st transform for a month, but as it got reduced, that's hopefully within your storage budget.

The 1st transform's destination index could use a `retention_policy` to delete data after a month.

(FWIW To safe storage, disable `_source` for both transforms)

---

<div class="post-metadata">

**Author:** ![Tobse](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/tobse/32/119154_2.png) [@Tobse](https://discuss.elastic.co/u/Tobse)\
**Post date:** [March 30, 2023, 3:24pm UTC](https://discuss.elastic.co/t/date-histogram-doesnt-work-with-calendar-interval-month/328848/9 "2023-03-30T15:24:01Z")

</div>

Thanks a lot for your detailed explanation 👍 This helped a lot!  
I thought, that in _continuous mode_, the magical _HyperLogLog_ will be stored or even held in memory. So it would be possible to index previous data and even update it with live data. To have this option, would be a real killer feature.

**Our simple use case** : Count the number of different products a users accesses per month.  
**Sample answer** : User Alex loads 4.921.465 unique products in Mai. The total number of loaded products in Mai were 306.357.360 (real numbers).

To also have a live preview for the month would be great, but fortunately in our case it's enough to calculate it afterwards. I wanted to skip the scanning of 600Gb historical data per month, because it takes a lot of time and produces a heavy load, to access this "cold" data (I know I reduce to load with loading limits).

In conclusion I need to disable the continuous mode for the transformation and optimize and fine-tune the aggregation of historical data.

---

<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 27, 2023, 3:24pm UTC](https://discuss.elastic.co/t/date-histogram-doesnt-work-with-calendar-interval-month/328848/10 "2023-04-27T15:24:37Z")

</div>

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