Search code examples
elasticsearchdatediff

How to calculate difference between two datetime in ElasticSearch

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
Solution

  • 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.