6

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!

fuzeto
  • 207
  • 1
  • 3
  • 8

2 Answers2

12

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.

Vineeth Mohan
  • 18,633
  • 8
  • 63
  • 77
  • It Work!!!! But I had to enable the dynamic scripting on config/elasticsearch.yml. See more in [here](http://stackoverflow.com/questions/24711168/elasticsearch-dynamic-scripting-disabled) Thanks!!!!! – fuzeto Sep 18 '15 at 19:03
8

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"
        }
        }
    ]
    }
}
}
bruce szalwinski
  • 724
  • 1
  • 8
  • 27
  • ElasticSearch 1.4 here - in my case I need to make all numbers decimals (instead of integer) to make division work. i.e. `/1000.0/86400.0` - in case that script part didn't work for you! – Atlas7 Mar 03 '21 at 14:01