ElasticSearch Date Histogram Aggregation considering dates within a Document range

Question

I'm working with documents in Elasticsearch that represent Alerts. These Alerts are activated for a time and then deactivated. They are similar to this schema.

{
     "id": 189393,
     "sensorId": "1111111",
     "activationTime": 1462569310000,
     "deactivationTime": 1462785524876,
}

I would like to know the number of active alerts per day. To achieve this I want to perform a Date Histogram Aggregation that returns the days between activation and deactivation and the number of active alerts per day.

What I've tried so far is this query.

{
   "query" : {
      ...
   },
   "aggs": {
    "active_alerts": {
      "date_histogram": {
        "field": "timestamp",
        "interval": "day"
      }
    }
  }
}

However, It returns just the day it was activated.

"aggregations": {
    "active_alerts": {
        "buckets": [
            {
                "key_as_string": "2016-05-06T00:00:00.000Z",
                "key": 1462492800000,
                "doc_count": 1
            }
        ]
    }
}

Which I'd like ​​to return are the days between activation and deactivation time and the number of active alerts per day, as shown below.

"aggregations": {
    "active_alerts": {
        "buckets": [
            {
                "key_as_string": "2016-05-06T00:00:00.000Z",
                "key": 1462492800000,
                "doc_count": 1
            },
            {
                "key_as_string": "2016-05-07T00:00:00.000Z",
                "key": 1462579200000,
                "doc_count": 1
            },
            {
                "key_as_string": "2016-05-08T00:00:00.000Z",
                "key": 1462665600000,
                "doc_count": 1
            }
        ]
    }
}

Thanks.

Answer 1

Finally I've found a solution via script, creating one that emits an array of dates from activation date until deactivation date.

"aggs": { 
    "active_alerts": {
      "date_histogram": {
        "interval": "day",
        "script":  "Date d1 = new Date(doc['activationTime'].value); Date d2 = new Date(doc['deactivationTime'].value); List<Date> dates = new ArrayList<Date>(); (d1..d2).each { date-> dates.add(date.toTimestamp().getTime())}; return dates;"
      }
    }
  }

Thanks.

Answer 2

I think you can only do it with scripted dateHistogram where you add the "missing" days from that interval you have programmatically:

  "aggs": {
    "active_alerts": {
      "date_histogram": {
        "interval": "day",
        "script": "counter=0;combinedDates=[];currentDate=doc.activationTime.date;while(currentDate.isBefore(doc.deactivationTime.date.getMillis())){combinedDates[counter++]=currentDate.getMillis();currentDate.addDays(1)};combinedDates[counter]=doc.deactivationTime.date.getMillis();return combinedDates"
      }
    }
  }