Convert duration to total milliseconds

I have a string field in my Elastic Search that has a duration in the format 00:00:00.000000. I want to convert that to a number field that is the total number of milliseconds.

Edit:

I thought of using a Scripted field but it isn't working.

try{
    String duration = doc['app.duration'].value;

    String[] durationParts = new String[3];
    
    int firstIdx = duration.indexOf(":");
    int secondIdx = duration.indexOf(":", firstIdx+1);
        
    durationParts[1] = duration.substring(firstIdx+1, secondIdx);
    durationParts[2] = duration.substring(secondIdx+1);
        
    long minutes = Long.parseLong(durationParts[1]) * 60000;
    float seconds = Float.parseFloat(durationParts[2]) * 1000;
    
    long mAndS = Math.round(minutes + seconds);
    return mAndS;
}
catch(Exception e) {
    return -1;
}

The above fails on the first line String duration = doc['app.duration'].value; and I get -1. However if I change that first line to a hard coded value like String duration = "00:00:01.5250000"; then I get out the expected 1525 that I expect.

I'm not sure what I'm doing wrong. From what I read using the doc object is how fields are accessed.

Solution

You might want to try the keyword sub-field instead as it contains the exact raw string value that you added into the source (i.e. 00:00:00.000000), while the app.duration field contains the analyzed text value which might not be what you expect depending on the analyzers you have set up.

 doc['app.duration.keyword'].value

Otherwise, a simpler script to get what you want, i.e. 0 and 1525, is the following:

    def parts = /:/.split(doc['app.duration'].value);
    def total = 0;
    total += Long.parseLong(parts[0]) * 24 * 60 * 1000;
    total += Long.parseLong(parts[1]) * 60 * 1000;
    total += Float.parseFloat(parts[2]) * 1000;
    return total;

Note that you need to add the following line to your elasticsearch.yml config file in order to enable the regular expression engine.

script.painless.regex.enabled: true