# Scripted field to calculate duration

**URL:** <https://discuss.elastic.co/t/scripted-field-to-calculate-duration/245222>\
**Category:** Kibana\
**Created:** [August 17, 2020, 10:52am UTC](https://discuss.elastic.co/t/scripted-field-to-calculate-duration/245222 "2020-08-17T10:52:20Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![hiba](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/hiba/32/110919_2.png) [@hiba](https://discuss.elastic.co/u/hiba)\
**Post date:** [August 17, 2020, 10:52am UTC](https://discuss.elastic.co/t/scripted-field-to-calculate-duration/245222/1 "2020-08-17T10:52:21Z")

</div>

Hi ,  
i try to create a new scripted field by calculating the difference(in minutes) between 2 dates in the following format :  
bookingStatus.confirmed :Jan 5, 2020 @ 11:30:00.000  
bookingStatus.wating :Jan 5, 2020 @ 09:00:00.000

i like the result be like : Xdays ,Xhours, Xminutes  
in this case duration = 0 days , 2 hours ,30minutes

i try this expression but i don't get the correct result:

def hours = doc['bookingStatus.confirmed'].value.getHour() - doc['bookingStatus.wating'].value.getHour();  
def minutes = doc['bookingStatus.confirmed'].value.getMinute() - doc['bookingStatus.wating'].value.getMinute();  
return (hours + minutes)

for the example above it return 32.

what can i do please i need help!

---

<div class="post-metadata">

**Author:** ![Marta\_Bondyra](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/marta_bondyra/32/102122_2.png) [@Marta\_Bondyra](https://discuss.elastic.co/u/Marta_Bondyra)\
**Post date:** [August 17, 2020, 11:27am UTC](https://discuss.elastic.co/t/scripted-field-to-calculate-duration/245222/2 "2020-08-17T11:27:30Z")

</div>

Hi @hiba,  
Are your dates saved as date field in Elasticsearch? If so, you can do:  
`doc['bookingStatus.confirmed'].value.millis - doc['bookingStatus.wating'].value.millis` will give you the value in miliseconds. If you set the custom format for the field to "Duration" you will also get a nice human readable format for that field.

---

<div class="post-metadata">

**Author:** ![hiba](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/hiba/32/110919_2.png) [@hiba](https://discuss.elastic.co/u/hiba)\
**Post date:** [August 17, 2020, 11:42am UTC](https://discuss.elastic.co/t/scripted-field-to-calculate-duration/245222/3 "2020-08-17T11:42:01Z")

</div>

Hi @Marta_Bondyra ,  
thank you for reply .

i get the result by this expression :  
def hours = doc['bookingStatus.confirmed'].value.getHour() - doc['bookingStatus.wating'].value.getHour();  
def minutes = doc['bookingStatus.confirmed'].value.getMinute() - doc['bookingStatus.wating'].value.getMinute();  
def days = doc['bookingStatus.confirmed'].value.dayOfMonth - doc['bookingStatus.wating'].value.dayOfMonth;  
return (days+'days,'+hours+'hours,'+minutes+'minutes')

 ![SFD](https://us1.discourse-cdn.com/elastic/original/3X/3/b/3beb4e3639824c1f3439b7c2f914bb11d7040c27.png)

the problem now that i can display this scripted field in any visualization

---

<div class="post-metadata">

**Author:** ![Marta\_Bondyra](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/marta_bondyra/32/102122_2.png) [@Marta\_Bondyra](https://discuss.elastic.co/u/Marta_Bondyra)\
**Post date:** [August 17, 2020, 11:57am UTC](https://discuss.elastic.co/t/scripted-field-to-calculate-duration/245222/4 "2020-08-17T11:57:02Z")

</div>

Hi @hiba,  
you cannot display it because Kibana doesn't see this value as a duration but as a string and so cannot parse it properly. Did you try the way I suggested? To make it readable, you can set the following options in scripted field panel:  
`type: number`  
`Format: Duration`  
`InputFormat: seconds` (if you use the date fields as normal UNIX timestamp)  
`OutputFormat: Human Readable`

You can also take a look here: [https://www.elastic.co/guide/en/elasticsearch/painless/current/painless-datetime.html#\_datetime\_difference\_elapsed\_time](https://www.elastic.co/guide/en/elasticsearch/painless/current/painless-datetime.html#_datetime_difference_elapsed_time) for more details. Let me know how it goes 🙂

---

<div class="post-metadata">

**Author:** ![hiba](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/hiba/32/110919_2.png) [@hiba](https://discuss.elastic.co/u/hiba)\
**Post date:** [August 17, 2020, 12:22pm UTC](https://discuss.elastic.co/t/scripted-field-to-calculate-duration/245222/5 "2020-08-17T12:22:44Z")

</div>

this is the result with the scripted field :

 ![sdf1](https://us1.discourse-cdn.com/elastic/original/3X/3/e/3e862a7d0a8282603edb87fc7c54067a3e5b9fb0.png)

for the Difference in milliseconds between two complex datetimes in the link can you please explaine to me how can i use it with this format :  
Jan 5, 2020 @ 11:30:00.000

---

<div class="post-metadata">

**Author:** ![Marta\_Bondyra](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/marta_bondyra/32/102122_2.png) [@Marta\_Bondyra](https://discuss.elastic.co/u/Marta_Bondyra)\
**Post date:** [August 17, 2020, 4:12pm UTC](https://discuss.elastic.co/t/scripted-field-to-calculate-duration/245222/6 "2020-08-17T16:12:04Z")

</div>

I made an error in the first response, probably that's why it didn't work, sorry! I see in your field list that your fields are of proper type, so it should be working with this configuration:

 ![image](https://us1.discourse-cdn.com/elastic/original/3X/b/f/bf00c32a86cb4eb348940ec3ed3575dab4e1b6f7.png)

---

<div class="post-metadata">

**Author:** ![hiba](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/hiba/32/110919_2.png) [@hiba](https://discuss.elastic.co/u/hiba)\
**Post date:** [August 18, 2020, 8:17am UTC](https://discuss.elastic.co/t/scripted-field-to-calculate-duration/245222/7 "2020-08-18T08:17:55Z")

</div>

Hi @Marta_Bondyra,

thank you it work , i have another question please !  
i want to calculate the sum of this durations and divide it by the number of bookings in my index.  
it very easy to do it with TSVB and use the bucket script . but the TSVB don't support the scripted fields .  
how can i do this !

---

<div class="post-metadata">

**Author:** ![Marta\_Bondyra](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/marta_bondyra/32/102122_2.png) [@Marta\_Bondyra](https://discuss.elastic.co/u/Marta_Bondyra)\
**Post date:** [August 18, 2020, 8:28am UTC](https://discuss.elastic.co/t/scripted-field-to-calculate-duration/245222/8 "2020-08-18T08:28:25Z")

</div>

I am afraid at this moment it's not possible in TSVB, unless someone else from the team has an idea? You could possibly use Vega, but it is not a trivial problem. The team is actively working on adding support for scripted fields in TSVB, here's the issue you can upvote: [https://github.com/elastic/kibana/issues/13928](https://github.com/elastic/kibana/issues/13928)

---

<div class="post-metadata">

**Author:** ![hiba](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/hiba/32/110919_2.png) [@hiba](https://discuss.elastic.co/u/hiba)\
**Post date:** [August 18, 2020, 8:43am UTC](https://discuss.elastic.co/t/scripted-field-to-calculate-duration/245222/9 "2020-08-18T08:43:58Z")

</div>

okey,  
thank you @Marta_Bondyra 🙂

---

<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:** [September 15, 2020, 8:44am UTC](https://discuss.elastic.co/t/scripted-field-to-calculate-duration/245222/10 "2020-09-15T08:44:07Z")

</div>

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