How to calculate overlap / elapsed time interval in elasticsearch?

Question

I have some entries in ES, these are different online meeting recordings that people join / leave at different times.

{name:"p1", join:'2017-11-17T00:01:00.293Z', leave: "2017-11-17T00:06:00.293Z"}
{name:"p2", join:'2017-11-17T00:02:00.293Z', leave: "2017-11-17T00:04:00.293Z"}
{name:"p3", join:'2017-11-17T00:03:00.293Z', leave: "2017-11-17T00:05:00.293Z"}

The time range may be something like this:

 p1: [============================================]
 p2:         [=================]
 p3:                  [==================]

The question is how to calculate the overlap time interval (total / meeting / total time), which should be 3 minutes

Another question is whether it is possible to find out when, when at this time there are 1/2/3 people? 2 minutes 2 people; 1 min. 3 persons

Answer 1

, ES. , , , , ,

.

1. , .

GET /meetings/_search

{
  "query": {
    "bool": {
      "must": [
        {
          "range": {
            "join": {
              "gte": "2007-10-01T00:00:00"
            }
          }
        },
        {
          "range": {
            "leave": {
              "lte": "2007-10-01T00:00:00"
            }
          }
        }
      ]
    }
  }
}

2. .
3. ,

Answer 2

max(join) min(leave):

GET your_index/your_type/_search
{
  "size": 0,
  "aggs": {
    "startTime": {
      "max": {
        "field": "join"
      }
    },
    "endTime": {
      "min": {
        "field": "leave"
      }
    }
  }
}

endTime-startTime Elasticsearch, bucket script aggregation. , .

, , : , , , Scripted Metric Aggregation.

(, ), .