# Calculating the difference between datetime cells for an average

Hello,

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

@Marco_Liberati
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);

emit(formattedTime);

} else {

emit(null)

}
``````

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) {
emit(
doc['start_datum'].value.until(
doc['eind_datum'].value,
java.time.temporal.ChronoUnit.MILLIS
)
)
}

``````
• 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?

Also, since 8.10 release, there's also a convenient `Duration` formatter in Lens you can use. Thanks that did the trick, still much to learn but thanks for the great help!