How to calculate difference between two datetime in ElasticSearch

Question

I'm working with ES and I need a query that returns the difference between two datetime (mysql timediff), but have not found any function of ES to do that. Someone who can help me?

MySQL Query

SELECT SEC_TO_TIME(
    AVG(
      TIME_TO_SEC(
        TIMEDIFF(r.acctstoptime,r.acctstarttime)
      )
    )
) as average_access

FROM radacct

Thanks!

Answer 1

Your best best is scripted fields. The above search query should work , provided you have enabled dynamic scripting and these date fields are defined as date in the mapping.

{
  "script_fields": {
    "test1": {
      "script": "doc['acctstoptime'].value - doc['acctstarttime'].value"
    }
  }
}

Note that you would be getting result in epoch , which you need to convert to your denomination.

You can read about scripted field here and some of its examples here.

Answer 2

Here is another example using script fields. It converts dates to milli seconds since epoch, subtracts the two and converts the results into number of days between the two dates.

{
"query": {
    "bool": {
    "must": [
        {
        "exists": {
            "field": "priorTransactionDate"
        }
        },
        {
        "script": {
            "script": "(doc['transactionDate'].date.millis - doc['priorTransactionDate'].date.millis)/1000/86400 < 365"
        }
        }
    ]
    }
}
}