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?

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.

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

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