Unable to calculate duration by 2 dates fields

[Rudolf_Reddy_Macejka]

Hello,

I am trying to crated new field in indices by recalculate duration based on response and request timestamps.

I have this document:

{
    "request" : {
      "time" : "2019-08-04T20:02:15.459Z"
    },
    "response" : {
      "time" : "2019-08-04T20:04:03.009Z"
    }
  }

Mapping is following:

{
  "my_index" : {
    "mappings" : {
      "properties" : {
        "request" : {
          "properties" : {
            "time" : {
              "type" : "date"
            }
          }
        },
        "response" : {
          "properties" : {
            "time" : {
              "type" : "date"
            }
          }
        }
      }
    }
  }
}

I am trying to run this script:

POST my_index/_update_by_query
{
    "script" : {
    "inline": "ctx._source.duration = (new SimpleDateFormat(\"yyyy-MM-dd'T'HH:mm:ss.SSSZ\").parse(ctx._source.response.time).getTime() - new SimpleDateFormat(\"yyyy-MM-dd'T'HH:mm:ss.SSSZ\").parse(ctx._source.request.time).getTime())"
  },
  "query": { "match_all": {} }
}

but getting error:

"script": "ctx._source.duration = (new SimpleDateFormat(\"yyyy-MM-dd'T'HH:mm:ss.SSSZ\").parse(ctx._source.response.time).getTime() - new SimpleDateFormat(\"yyyy-MM-dd'T'HH:mm:ss.SSSZ\").parse(ctx._source.request.time).getTime())",
"lang": "painless",
"caused_by": {
  "type": "null_pointer_exception",
  "reason": null
}

I do not see there any problem or did I overlooked something?

Thank you for help
Cheers, Reddy

[Rudolf_Reddy_Macejka]

I have tried an alternative

GET my_index/_search
{
  "script_fields": {
    "millisDuration": {
      "script": {
        "lang": "painless",
        "source": "doc['response.time'].date.millisOfDay - doc['request.time'].date.millisOfDay"
      }
    }
  }
}

but also failing on:

  "failed_shards": [
      {
        "shard": 0,
        "index": "my_index",
        "node": "-YfHgraxR7eDId2lQbXwnA",
        "reason": {
          "type": "script_exception",
          "reason": "runtime error",
          "script_stack": [
            "doc['response.time'].date.millisOfDay - doc['request.time'].date.millisOfDay",
            "                    ^---- HERE"
          ],
          "script": "doc['response.time'].date.millisOfDay - doc['request.time'].date.millisOfDay",
          "lang": "painless",
          "caused_by": {
            "type": "illegal_argument_exception",
            "reason": "Illegal list shortcut value [date]."
          }
        }
      }
    ]

[spinscale (Alexander Reelsen)]

how about this

DELETE test

PUT test/_doc/1?refresh
{
  "request": {
    "time": "2019-08-04T20:02:15.459Z"
  },
  "response": {
    "time": "2019-08-04T20:04:03.009Z"
  }
}

POST test/_update_by_query
{
    "script" : {
      "lang" : "painless",
    "source": "ctx._source.duration_in_ms = ZonedDateTime.parse(ctx._source.response.time).toInstant().toEpochMilli() - ZonedDateTime.parse(ctx._source.request.time).toInstant().toEpochMilli()"
  }
}

GET test/_doc/1

[Rudolf_Reddy_Macejka]

Thank you so much, this works perfectly!