Select average time between two properties in elasticsearch index

user_324307 0
Viewed 73

Given an elasticsearch index with two date fields, how can I query for the average time between those two dates?

{
    "mappings": {
        "properties": {
            "dateInserted": {
                "type": "date",
                "format": "dateOptionalTime"
            },
            "dateUpdated": {
                "type": "date",
                "format": "dateOptionalTime"
            }
        }
    }
}

[
    {
        "dateInserted": "2020-10-01 00:00:00",
        "dateUpdated": "2020-10-01 01:00:00"
    }, {
        "dateInserted": "2020-10-01 02:00:00",
        "dateUpdated": "2020-10-01 04:00:00"
    }
]

Interval for document A is one hour, interval for document B is two hours, therefore expected result would be 1.5 hours.

elasticsearch
1 Answers

An avg aggregation script should be fine:

{
  "size": 0,
  "aggs": {
    "avg_duration": {
      "avg": {
        "script": {
          "source": """
            def duration = doc.dateUpdated.value.getMillis() - doc.dateInserted.value.getMillis();
            return duration / (float) (1000*60*60)
          """
        }
      }
    }
  }
}

or, more compact:

{
 ...
 "source": "def duration = doc.dateUpdated.value.getMillis() - doc.dateInserted.value.getMillis(); return duration / (float) (1000*60*60)"
 ...
}
user_81603180
Related