Elastic Search count between dates

Question

I have elastic search and kibana version 7.X

I have a document with date fields:

1. start_date
2. end_date
3. created_at

I wanted to get the number of people I have in my database by month --> A line chart with x-axis date histogram and count on y-axis did the trick.

Now I would like to have the number of active people by month. An active person is one where the current date is between the start and end date. I can't seem to know how to proceed here and apply the appropriate filters

Is this possible with Kibana?

---

I was able to find a partial solution by using filters. But here I create the values manually and hardcode the ranges. Any way to automate this?

Answer 1

The correct way of doing this is to create an additional date_range field (e.g. named activity_period) in your document like this:

{
   "user": "user1",
   "start_date": "2020-01-01",
   "end_date": "2020-05-01",
   "activity_period": {
     "gte": "2020-01-01",
     "lte": "2020-05-01"
   }
}

Using that new activity_period field you can now run a date_histogram aggregation on it and each time interval within the gte and lte dates will count as 1.

POST index/_search
{
  "aggs": {
    "histo": {
      "date_histogram": {
        "field": "activity_period",
        "interval": "month"
      }
    }
  }
}

For each monthly bucket, you're going to get the number of users that were active that month.

The only issue is that Kibana doesn't support date_histogram on date_range fields yet. So I'm afraid that until this issue gets fixed, the only way you can do this in Kibana is by actually storing all months during which a user was active in an array, like this:

{
   "user": "user1",
   "start_date": "2020-01-01",
   "end_date": "2020-05-01",
   "activity_period": [
     "2020-01-01",
     "2020-02-01",
     "2020-03-01",
     "2020-04-01",
     "2020-05-01"
   ]
}

If you do this, you'll be able to do the visualization you need in Kibana by configuring a date_histogram aggregation on the activity_period date array field.