# Unable to calculate duration by 2 dates fields

**URL:** <https://discuss.elastic.co/t/unable-to-calculate-duration-by-2-dates-fields/194833>\
**Category:** Elasticsearch\
**Created:** [August 12, 2019, 11:11am UTC](https://discuss.elastic.co/t/unable-to-calculate-duration-by-2-dates-fields/194833 "2019-08-12T11:11:20Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![Rudolf\_Reddy\_Macejka](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rudolf_reddy_macejka/32/12875_2.png) [@Rudolf\_Reddy\_Macejka](https://discuss.elastic.co/u/Rudolf_Reddy_Macejka)\
**Post date:** [August 12, 2019, 11:11am UTC](https://discuss.elastic.co/t/unable-to-calculate-duration-by-2-dates-fields/194833/1 "2019-08-12T11:11:20Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Rudolf\_Reddy\_Macejka](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rudolf_reddy_macejka/32/12875_2.png) [@Rudolf\_Reddy\_Macejka](https://discuss.elastic.co/u/Rudolf_Reddy_Macejka)\
**Post date:** [August 12, 2019, 11:35am UTC](https://discuss.elastic.co/t/unable-to-calculate-duration-by-2-dates-fields/194833/2 "2019-08-12T11:35:20Z")

</div>

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]."
          }
        }
      }
    ]
```

---

<div class="post-metadata">

**Author:** ![spinscale](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/spinscale/32/25011_2.png) [@spinscale](https://discuss.elastic.co/u/spinscale)\
**Post date:** [August 12, 2019, 12:32pm UTC](https://discuss.elastic.co/t/unable-to-calculate-duration-by-2-dates-fields/194833/3 "2019-08-12T12:32:54Z")

</div>

how about this

```auto
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

```

---

<div class="post-metadata">

**Author:** ![Rudolf\_Reddy\_Macejka](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rudolf_reddy_macejka/32/12875_2.png) [@Rudolf\_Reddy\_Macejka](https://discuss.elastic.co/u/Rudolf_Reddy_Macejka)\
**Post date:** [August 13, 2019, 8:21am UTC](https://discuss.elastic.co/t/unable-to-calculate-duration-by-2-dates-fields/194833/4 "2019-08-13T08:21:48Z")

</div>

Thank you so much, this works perfectly!

---

<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 10, 2019, 8:21am UTC](https://discuss.elastic.co/t/unable-to-calculate-duration-by-2-dates-fields/194833/5 "2019-09-10T08:21:54Z")

</div>

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