Calculating the difference between datetime cells for an average

[SpicyS (SpicyS Steam)]

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?

[Marco_Liberati]

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?

[SpicyS (SpicyS Steam)]

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

[SpicyS (SpicyS Steam)]

@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

[Marco_Liberati]

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:

[SpicyS (SpicyS Steam)]

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?

[Marco_Liberati]

The runtime field must be a long type:

[Marco_Liberati]

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

[SpicyS (SpicyS Steam)]

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