# Calculating the difference between datetime cells for an average

**URL:** <https://discuss.elastic.co/t/calculating-the-difference-between-datetime-cells-for-an-average/343225>\
**Category:** Kibana\
**Tags:** lens\
**Created:** [September 18, 2023, 7:55am UTC](https://discuss.elastic.co/t/calculating-the-difference-between-datetime-cells-for-an-average/343225 "2023-09-18T07:55:28Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![SpicyS](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/spicys/32/125743_2.png) [@SpicyS](https://discuss.elastic.co/u/SpicyS)\
**Post date:** [September 18, 2023, 7:55am UTC](https://discuss.elastic.co/t/calculating-the-difference-between-datetime-cells-for-an-average/343225/1 "2023-09-18T07:55:28Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![Marco\_Liberati](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/marco_liberati/32/82953_2.png) [@Marco\_Liberati](https://discuss.elastic.co/u/Marco_Liberati)\
**Post date:** [September 18, 2023, 10:23am UTC](https://discuss.elastic.co/t/calculating-the-difference-between-datetime-cells-for-an-average/343225/2 "2023-09-18T10:23:42Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![SpicyS](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/spicys/32/125743_2.png) [@SpicyS](https://discuss.elastic.co/u/SpicyS)\
**Post date:** [September 18, 2023, 10:57am UTC](https://discuss.elastic.co/t/calculating-the-difference-between-datetime-cells-for-an-average/343225/3 "2023-09-18T10:57:10Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![SpicyS](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/spicys/32/125743_2.png) [@SpicyS](https://discuss.elastic.co/u/SpicyS)\
**Post date:** [September 19, 2023, 6:47am UTC](https://discuss.elastic.co/t/calculating-the-difference-between-datetime-cells-for-an-average/343225/4 "2023-09-19T06:47:05Z")

</div>

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

```auto
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

---

<div class="post-metadata">

**Author:** ![Marco\_Liberati](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/marco_liberati/32/82953_2.png) [@Marco\_Liberati](https://discuss.elastic.co/u/Marco_Liberati)\
**Post date:** [September 19, 2023, 7:33am UTC](https://discuss.elastic.co/t/calculating-the-difference-between-datetime-cells-for-an-average/343225/5 "2023-09-19T07:33:52Z")

</div>

I think you can do the following:

- define a `duration` runtime field as the difference in milliseconds between two dates:

```auto
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:

 ![Screenshot 2023-09-19 at 09.33.32](https://us1.discourse-cdn.com/elastic/original/3X/0/0/00d76c1de1af88307d7d22160e5c43e567cacdde.png)

---

<div class="post-metadata">

**Author:** ![SpicyS](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/spicys/32/125743_2.png) [@SpicyS](https://discuss.elastic.co/u/SpicyS)\
**Post date:** [September 19, 2023, 8:00am UTC](https://discuss.elastic.co/t/calculating-the-difference-between-datetime-cells-for-an-average/343225/6 "2023-09-19T08:00:21Z")

</div>

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

 ![image](https://us1.discourse-cdn.com/elastic/original/3X/3/2/3219795a54fd18e9b80869822cf063444a816564.png)  
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?

---

<div class="post-metadata">

**Author:** ![Marco\_Liberati](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/marco_liberati/32/82953_2.png) [@Marco\_Liberati](https://discuss.elastic.co/u/Marco_Liberati)\
**Post date:** [September 19, 2023, 8:43am UTC](https://discuss.elastic.co/t/calculating-the-difference-between-datetime-cells-for-an-average/343225/7 "2023-09-19T08:43:48Z")

</div>

The runtime field must be a `long` type:

 ![Screenshot 2023-09-19 at 10.43.20](https://us1.discourse-cdn.com/elastic/original/3X/e/f/eff397b25f6406fc525553848b7bf926b7a25841.png)

---

<div class="post-metadata">

**Author:** ![Marco\_Liberati](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/marco_liberati/32/82953_2.png) [@Marco\_Liberati](https://discuss.elastic.co/u/Marco_Liberati)\
**Post date:** [September 19, 2023, 8:45am UTC](https://discuss.elastic.co/t/calculating-the-difference-between-datetime-cells-for-an-average/343225/8 "2023-09-19T08:45:17Z")

</div>

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](https://us1.discourse-cdn.com/elastic/original/3X/a/3/a3287ac1a1729169367eb4ec0a99d1cbc96e56ef.png)

---

<div class="post-metadata">

**Author:** ![SpicyS](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/spicys/32/125743_2.png) [@SpicyS](https://discuss.elastic.co/u/SpicyS)\
**Post date:** [September 19, 2023, 8:46am UTC](https://discuss.elastic.co/t/calculating-the-difference-between-datetime-cells-for-an-average/343225/9 "2023-09-19T08:46:54Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [October 17, 2023, 8:47am UTC](https://discuss.elastic.co/t/calculating-the-difference-between-datetime-cells-for-an-average/343225/10 "2023-10-17T08:47:08Z")

</div>

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