How to do a minus operation on time-stamps in elasticsearch?

I looked at the example given for scripted metric aggregation and modified it for this problem:

{   "aggs": {      "actions": {         "terms": {            "field": "action_id"         },         "aggs": {            "duration": {               "scripted_metric": {                  "init_script": "_agg['delta'] = 0",                  "map_script": "if (doc['action'].value == \"complete\"){ _agg.delta += doc['time'].value } else {_agg.delta -= doc['time'].value}",                  "combine_script": "return _agg.delta",                  "reduce_script": "duration = 0; for (d in _aggs) { duration += d }; return duration"               }            }         }      }   }}

First it creates buckets for each action_id with terms aggregation.

Then for each bucket it calculates a scripted metric.

On map step it takes 'complete' timestamps as positive values and others (i.e. 'start' ones) as negative for each shard. Then on combine step it just returns them. And on reduce step it sums durations for an action over all the shards (as 'start' and 'complete' events could be on different shards) to get actual duration.

I'm not sure about the performance of this aggregation but you can try it out on your dataset. And please note that it is marked as experimental functionality yet.

It looks like elasticsearch is not designed to calculate time duration directly. It seems like elasticsearch uses logstash to perform such tasks.

https://www.elastic.co/guide/en/logstash/current/plugins-filters-elasticsearch.html

if [action] == "complete" {   elasticsearch {      hosts => ["es-server"]      query => "action:start AND action_id:%{[action_id]}"      fields => ["time", "started"]   }  date {     match => ["[started]", "ISO8601"]     target => "[started]"  }  ruby {     code => "event['duration_hrs'] = (event['@timestamp'] - event['started']) / 3600 rescue nil"     }}