# Calculate time difference between 2 date fields : scripted field

**URL:** <https://discuss.elastic.co/t/calculate-time-difference-between-2-date-fields-scripted-field/248730>\
**Category:** Elasticsearch\
**Created:** [September 15, 2020, 7:11pm UTC](https://discuss.elastic.co/t/calculate-time-difference-between-2-date-fields-scripted-field/248730 "2020-09-15T19:11:10Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![xyz2](https://avatars.discourse-cdn.com/v4/letter/x/e480ec/32.png) [@xyz2](https://discuss.elastic.co/u/xyz2)\
**Post date:** [September 15, 2020, 7:11pm UTC](https://discuss.elastic.co/t/calculate-time-difference-between-2-date-fields-scripted-field/248730/1 "2020-09-15T19:11:10Z")

</div>

HI,

I need to calculate duration using 2 date fields in watcher API. I tried using painless script and to test using scripted field. But it is errors out stating invalid syntax. What is missing in below ?

if (!doc.containsKey('job\_end\_date') || doc['job\_end\_date'].empty))  
{(doc['job\_end\_date'].value.toInstant().toEpochMilli() - doc['job\_start\_date'].value.toInstant().toEpochMilli())  
}  
else  
{ return 0; }

---

<div class="post-metadata">

**Author:** ![Emanuil](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/emanuil/32/36783_2.png) [@Emanuil](https://discuss.elastic.co/u/Emanuil)\
**Post date:** [September 15, 2020, 8:21pm UTC](https://discuss.elastic.co/t/calculate-time-difference-between-2-date-fields-scripted-field/248730/2 "2020-09-15T20:21:54Z")

</div>

> [@xyz2](#):
>
> if (!doc.containsKey('job\_end\_date') || doc['job\_end\_date'].empty))  
> {(doc['job\_end\_date'].value.toInstant().toEpochMilli() - doc['job\_start\_date'].value.toInstant().toEpochMilli())  
> }  
> else  
> { return 0; }

Firstly, I get an exception since newlines are not allowed in the JSON _source_. Put it all on one line.

I can't try this in Watcher right now. But here's something that works against the Painless Execute API as if I were running it in a function\_score query. I'm just using the context because it's the only one that supports reading from a doc and returning a number - you're trying to return 0 or number of milliseconds. You may need to adjust slightly for Watcher!

Do these in the Kibana Dev Console to try for yourself:

```auto
PUT myindex

PUT myindex/_mapping
{
  "properties" : {
    "job_end_date" : {
      "type" : "date"
    },
    "job_start_date" : {
      "type" : "date"
    }
  }
}

POST /_scripts/painless/_execute
{
  "script": {
    "source": "if (doc.containsKey('job_start_date') && doc['job_start_date'].size() != 0 && doc.containsKey('job_end_date') && doc['job_end_date'].size() != 0) { doc['job_end_date'].value.toInstant().toEpochMilli() - doc['job_start_date'].value.toInstant().toEpochMilli() } else { 0; }"
  },
  "context": "score",
  "context_setup": {
    "index": "myindex",
    "document": {
      "job_start_date" : "2020-01-29T04:33:32Z",
      "job_end_date" : "2020-01-29T05:33:32Z"
    }
  }
}

```

You should get

```auto
{
  "result" : 3600000.0
}

```

In addition to the newlines I've:

- removed the return in the else
- changed the conditional to check both date fields exist and that they're not empty (the recommended way is via `.size() != 0`). I think this is what you were trying to do but correct me if wrong.

---

<div class="post-metadata">

**Author:** ![xyz2](https://avatars.discourse-cdn.com/v4/letter/x/e480ec/32.png) [@xyz2](https://discuss.elastic.co/u/xyz2)\
**Post date:** [September 15, 2020, 9:04pm UTC](https://discuss.elastic.co/t/calculate-time-difference-between-2-date-fields-scripted-field/248730/3 "2020-09-15T21:04:28Z")

</div>

Thank you . That works.  
If end date doesn't exist, I need to calculate (now - start date)

---

<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:** [October 13, 2020, 9:04pm UTC](https://discuss.elastic.co/t/calculate-time-difference-between-2-date-fields-scripted-field/248730/4 "2020-10-13T21:04:55Z")

</div>

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