Histogram to show object count for missing days in date histogram

[SUMANT_MISHRA]

Hi,

I need support in creating a histogram that can show the count of objects on a daily basis if the date (in the histogram) falls within a date range. It should show count of the objects even if there is no object available for any particular date of the date field.For example, date field contains objects for 2nd August, 4th August and 8th August. But histogram should show the object count of the missing dates like 3rd, 5th, 6th and 7th.

Data structure:

{
  "SiteId" : 1,
  "StartDate" : "2021-07-31",
  "EndDate" : "2021-08-04",
  "CreatedAt" : "2021-08-02T23:11:25.84"
}
{
  "SiteId" : 2,
  "StartDate" : "2021-08-03",
  "EndDate" : "2021-08-05",
  "CreatedAt" : "2021-08-04T23:11:25.84"
}
{
  "SiteId" : 3,
  "StartDate" : "2021-08-03",
  "EndDate" : "2021-08-10",
  "CreatedAt" : "2021-08-08T23:11:25.84"
}

Expected output:

Appreciate any help in advance.

[Marius_Dragomir]

When creating a histogram in Lens you now have the option to show Empty buckets as well. Just do a split per day for the histogram and a count metric for the objects.

[SUMANT_MISHRA]

Hi @Marius_Dragomir, the challenge is there how to get the count for empty buckets? I need to get the count if the key_as_string value of the dates on histogram falls between StartDate and EndDate. I am trying to get an output like:

{
  "Date" : "2021-07-31",
  "ActiveCount" : 1,
}
{
  "Date" : "2021-08-01",
  "ActiveCount" : 1,
}
{
  "Date" : "2021-08-2",
  "ActiveCount" : 3,
}
{
  "Date" : "021-08-3",
  "ActiveCount" : 3,
}
{
  "Date" : "021-08-4",
  "ActiveCount" : 2,
}
{
  "Date" : "021-08-5",
  "ActiveCount" : 1,
}
{
  "Date" : "021-08-6",
  "ActiveCount" : 1,
}
{
  "Date" : "021-08-7",
  "ActiveCount" : 1,
}

I can also see an open question on stackoverflow related to this:

Elasticsearch date histogram: calculate histogram 'field' from start and stop date?