Painless Script to calculate time difference between 2 dates

Hi I have 2 columns as date datatype in my Kibana Index Pattern and I want to add a third column as the difference between the 2 columns:
Here is sample and format of the columns:

Col1: Jun 16, 2022 , 09:00:00.000
Col2: Jun 16, 2022 , 09:10:07.999

Now I want to have a third column as TimeDifference in the following format:

TimeDiff: 00:10:07.999

Can anyone help me with the script please I tried a few but everytime it complains about invalid script, here is the script that I tried and it failed:

def diffInMs = doc['col2'].value.getMillis() - doc['col1'].value.getMillis();
def duration = Duration.ofMillis(diffInMs);
def hours = duration.toHours();
def minutes = duration.minusHours(hours).toMinutes();
def seconds = duration.minusHours(hours).minusMinutes(minutes).getSeconds();
def str = hours + ':' + minutes + ':' + seconds;
return str;

It would be really helpful if anyone could point out what did I miss out in this script.
Thanks

Hi @Avnish_Singh .

I don't know if it's exactly like your example but with your script code I got the desired output.

POST teste/_doc
{
  "col1": "2022-06-16T09:00:00.000",
  "col2": "2022-06-16T09:10:07.999"
}

GET teste/_search
{
  "script_fields": {
    "time_diff": {
      "script": {
        "source": """
          def diffInMs = doc['col2'].value.getMillis() - doc['col1'].value.getMillis();
          return new SimpleDateFormat('HH:mm:ss.SSS').format(diffInMs);
      """
      }
    }
  }
}
1 Like

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