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

Youmans

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.

Wideawake

It Work!!!! But I had to enable the dynamic scripting on config/elasticsearch.yml. See more in here Thanks!!!!! – Youmans

Unfortunately this doesn't work for me... Maybe something was updated since 2015... – Persistence

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"
        }
        }
    ]
    }
}
}

Quinquereme

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! – Skiver

Answer 3

Scripted fields work, but unfortunately accepted answer doesn't work for me in 2023 with Elastic 8.10... After a lot of tries, I came with the following which works:

GET yourcustomindex/_search
{
  "script_fields": {
    "duration": {
      "script": {
        "source": "doc['endDate'].getValue().toEpochSecond() - doc['startDate'].getValue().toEpochSecond()"
      }
    }
  }
}

Mind toEpochSecond in contrast with toEpochSecond*s* which you can also find in some answers, however it also seems outdated...

Persistence