Scripted field to calculate duration

[hiba (chaieb)]

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!

[Marta_Bondyra]

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.

[hiba (chaieb)]

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')

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

[Marta_Bondyra]

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 for more details. Let me know how it goes

[hiba (chaieb)]

this is the result with the scripted field :

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

[Marta_Bondyra]

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:

[hiba (chaieb)]

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 !

[Marta_Bondyra]

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

[hiba (chaieb)]

okey,
thank you @Marta_Bondyra