Calculating the difference between datetime cells for an average


I'm new to kibana and I would like to know if it is possible to calculate the difference between 2 datetime fields named: "start_date" and "end_date", the reason for this is the fact I want to show the average duration per log, but in the visualization formula menu it tells me I need a string.

Could someone help me with this?

Hi @SpicyS ,

welcome to the Kibana community.
Can I ask what version of the stack are you using?
also, it would be useful to know what's the mapping of both fields: are they 2 text/keyword fields or dates?

Thanks for your reply! I'm using v8.8.1 and the mappings are both date

Okay so I made some progress, first I made a custom field:

if (!doc['start_datum'].empty && !doc['eind_datum'].empty) {

    def startDatum = doc['start_datum'].value;

    def eindDatum = doc['eind_datum'].value;

    def timeDifference = Math.abs(eindDatum.toInstant().toEpochMilli() - startDatum.toInstant().toEpochMilli());

    def seconds = (timeDifference / 1000).intValue();

    def milliseconds = (timeDifference % 1000).intValue();

    def formattedTime = String.valueOf(seconds) + ":" + String.valueOf(milliseconds);


} else {



But now in my visualization I can't calculate the average, so could this be a nesting issue? Still pretty new to this

I think you can do the following:

  • define a duration runtime field as the difference in milliseconds between two dates:
if (!doc['start_datum'].empty && !doc['eind_datum'].empty) {

  • then in Lens use the Average operation:

Wow, I liked how you just made the code so much simpler! I tried it and it gives me the following error:

So I tried converting it to a string, but then I still can't calculate the average. Could it be that that the data set is not correctly configured?

The runtime field must be a long type:

Also, since 8.10 release, there's also a convenient Duration formatter in Lens you can use.

Screenshot 2023-09-19 at 10.45.05

Thanks that did the trick, still much to learn :slight_smile: but thanks for the great help!

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.