CODEWITHSUNDEEP
elasticsearchdatediff
fuzeto
fuzeto

Reputation: 247

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

Upvotes: 7

Views: 25989

Answers (3)

okainov
okainov

Reputation: 4671

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

Upvotes: 0

bruce szalwinski
bruce szalwinski

Reputation: 752

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

Upvotes: 9

Vineeth Mohan
Vineeth Mohan

Reputation: 19283

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.

Upvotes: 12

Related Questions