How to calculate difference between two datetime in ElasticSearch

elasticsearch datediff
fuzeto picture fuzeto · Sep 18, 2015 · Viewed 17k times · Source

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

Vineeth Mohan picture Vineeth Mohan · Sep 18, 2015

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.

Related questions

Elasticsearch query to return all records

I have a small database in Elasticsearch and for testing purposes would like to pull all records back. I am attempting to use a URL of the form... http://localhost:9200/foo/_search?pretty=true&q={'matchAll':{''}} Can someone …

Read More
Making a request to a RESTful API using python

I have a RESTful API that I have exposed using an implementation of Elasticsearch on an EC2 instance to index a corpus of content. I can query the search by running the following from my terminal (MacOSX): curl -XGET 'http://…

Read More
Removing Data From ElasticSearch

I'm new to ElasticSearch. I'm trying to figure out how to remove data from ElasticSearch. I have deleted my indexes. However, that doesn't seem to actually remove the data itself. The other stuff I've seen points to the Delete by …

Read More