# Painless script to calculate elapsed time between two fields

**URL:** <https://discuss.elastic.co/t/painless-script-to-calculate-elapsed-time-between-two-fields/275133>\
**Category:** Kibana\
**Created:** [June 7, 2021, 10:16am UTC](https://discuss.elastic.co/t/painless-script-to-calculate-elapsed-time-between-two-fields/275133 "2021-06-07T10:16:30Z")\
**Posts on this page:** 15\
**Page:** 1

<div class="post-metadata">

**Author:** ![theirfan](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/theirfan/32/89339_2.png) [@theirfan](https://discuss.elastic.co/u/theirfan)\
**Post date:** [June 7, 2021, 10:16am UTC](https://discuss.elastic.co/t/painless-script-to-calculate-elapsed-time-between-two-fields/275133/1 "2021-06-07T10:16:30Z")

</div>

> [@Trying to calculate time difference using scripted fields](https://discuss.elastic.co/t/trying-to-calculate-time-difference-using-scripted-fields/197674/5):
>
> please try the following doc['netflow.last\_switched'].value.millis - doc['netflow.first\_switched'].value.millis

Hi All,

i have two fields,  
up : "2021-06-03T07:01:56.159Z"  
down : "2021-06-03T10:01:29.978Z"

Following the above link and solution,  
`doc['down'].value.millis - doc['up'].value.millis`

shows me _`Script is invalid. View script preview for details`_ in painless scripted tab.

Type : date  
Format : Date

Please advise.

---

<div class="post-metadata">

**Author:** ![flash1293](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/flash1293/32/41227_2.png) [@flash1293](https://discuss.elastic.co/u/flash1293)\
**Post date:** [June 7, 2021, 1:26pm UTC](https://discuss.elastic.co/t/painless-script-to-calculate-elapsed-time-between-two-fields/275133/2 "2021-06-07T13:26:57Z")

</div>

> _`View script preview for details`_

What does it show in script preview?

---

<div class="post-metadata">

**Author:** ![theirfan](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/theirfan/32/89339_2.png) [@theirfan](https://discuss.elastic.co/u/theirfan)\
**Post date:** [June 7, 2021, 1:30pm UTC](https://discuss.elastic.co/t/painless-script-to-calculate-elapsed-time-between-two-fields/275133/3 "2021-06-07T13:30:17Z")

</div>

There nothing in script preview. FYI, i am running 7.13.1 and it shows that

> Scripted fields are deprecated

Attached is screenshot of script window.

 ![elk](https://us1.discourse-cdn.com/elastic/original/3X/1/7/1754cad2fd789baabdb3c91f9a8a2271ecc62c48.png)

---

<div class="post-metadata">

**Author:** ![flash1293](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/flash1293/32/41227_2.png) [@flash1293](https://discuss.elastic.co/u/flash1293)\
**Post date:** [June 7, 2021, 1:32pm UTC](https://discuss.elastic.co/t/painless-script-to-calculate-elapsed-time-between-two-fields/275133/4 "2021-06-07T13:32:34Z")

</div>

You can see the script preview by clicking "Get help with the syntax and preview the results of your script". Could you click that and do a screenshot of the flyout that opens?

---

<div class="post-metadata">

**Author:** ![theirfan](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/theirfan/32/89339_2.png) [@theirfan](https://discuss.elastic.co/u/theirfan)\
**Post date:** [June 7, 2021, 1:36pm UTC](https://discuss.elastic.co/t/painless-script-to-calculate-elapsed-time-between-two-fields/275133/5 "2021-06-07T13:36:13Z")

</div>

```
There's an error in your script

{
 "rootcause": [
  {
   "type": "scriptexception",
   "reason": "runtime error",
   "scriptstack": [
    "org.elasticsearch.index.fielddata.ScriptDocValues$Dates.get(ScriptDocValues.java:149)",
    "org.elasticsearch.index.fielddata.ScriptDocValues$Dates.getValue(ScriptDocValues.java:143)",
    "doc['down'].value.millis - doc['up'].value.millis",
    " ^---- HERE"
   ],
   "script": "doc['down'].value.millis - doc['up'].value.millis",
   "lang": "painless",
   "position": {
    "offset": 54,
    "start": 0,
    "end": 67
   }
  }
 ],
 "type": "searchphaseexecutionexception",
 "reason": "all shards failed",
 "phase": "query",
 "grouped": true,
 "failedshards": [
  {
   "shard": 0,
   "index": "durations",
   "node": "WnIzR55T5iBqhPS2vP9lw",
   "reason": {
    "type": "scriptexception",
    "reason": "runtime error",
    "scriptstack": [
     "org.elasticsearch.index.fielddata.ScriptDocValues$Dates.get(ScriptDocValues.java:149)",
     "org.elasticsearch.index.fielddata.ScriptDocValues$Dates.getValue(ScriptDocValues.java:143)",
     "doc['down'].value.millis - doc['up'].value.millis",
     " ^---- HERE"
    ],
    "script": "doc['down'].value.millis - doc['up'].value.millis",
    "lang": "painless",
    "position": {
     "offset": 54,
     "start": 0,
     "end": 67
    },
    "causedby": {
     "type": "illegalstateexception",
     "reason": "A document doesn't have a value for a field! Use doc[<field>].size()==0 to check if a document is missing a field!"
    }
   }
  }
 ]
}
```

---

<div class="post-metadata">

**Author:** ![flash1293](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/flash1293/32/41227_2.png) [@flash1293](https://discuss.elastic.co/u/flash1293)\
**Post date:** [June 7, 2021, 1:37pm UTC](https://discuss.elastic.co/t/painless-script-to-calculate-elapsed-time-between-two-fields/275133/6 "2021-06-07T13:37:25Z")

</div>

The "reason" tells you what's going on:

> [@theirfan](#):
>
> ```auto
> "reason": "A document doesn't have a value for a field! Use doc[<field>].size()==0 to check if a document is missing a field!"
> 
> ```

Can't put that into better words

---

<div class="post-metadata">

**Author:** ![theirfan](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/theirfan/32/89339_2.png) [@theirfan](https://discuss.elastic.co/u/theirfan)\
**Post date:** [June 7, 2021, 1:52pm UTC](https://discuss.elastic.co/t/painless-script-to-calculate-elapsed-time-between-two-fields/275133/7 "2021-06-07T13:52:04Z")

</div>

I doubt that as "up" and "down" fields have timestamps in them.

 ![up-down](https://us1.discourse-cdn.com/elastic/original/3X/d/8/d81fcd527ac483861a31a7feaf0785e1947f1aff.png)

---

<div class="post-metadata">

**Author:** ![flash1293](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/flash1293/32/41227_2.png) [@flash1293](https://discuss.elastic.co/u/flash1293)\
**Post date:** [June 7, 2021, 1:54pm UTC](https://discuss.elastic.co/t/painless-script-to-calculate-elapsed-time-between-two-fields/275133/8 "2021-06-07T13:54:30Z")

</div>

A scripted field is executed once per document. As you can see in your screenshot, every document either has `up` or `down`, but never both. A scripted field can't access values from different documents.

---

<div class="post-metadata">

**Author:** ![theirfan](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/theirfan/32/89339_2.png) [@theirfan](https://discuss.elastic.co/u/theirfan)\
**Post date:** [June 7, 2021, 1:58pm UTC](https://discuss.elastic.co/t/painless-script-to-calculate-elapsed-time-between-two-fields/275133/9 "2021-06-07T13:58:10Z")

</div>

oh, i see now!

i was trying to calculate the time difference between two timestamps. Isn't this proper way to do it using painless script?

As these fields already have timestamps in them. If this isn't possible, can you please guide me to another way to obtain this?

Much appreciated!

---

<div class="post-metadata">

**Author:** ![theirfan](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/theirfan/32/89339_2.png) [@theirfan](https://discuss.elastic.co/u/theirfan)\
**Post date:** [June 8, 2021, 6:47am UTC](https://discuss.elastic.co/t/painless-script-to-calculate-elapsed-time-between-two-fields/275133/10 "2021-06-08T06:47:50Z")

</div>

Anyone ?

---

<div class="post-metadata">

**Author:** ![flash1293](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/flash1293/32/41227_2.png) [@flash1293](https://discuss.elastic.co/u/flash1293)\
**Post date:** [June 8, 2021, 9:00am UTC](https://discuss.elastic.co/t/painless-script-to-calculate-elapsed-time-between-two-fields/275133/11 "2021-06-08T09:00:26Z")

</div>

You need to merge the documents so up and down are fields on the same document. One way to do this is using transforms with the `latest` method: [Transforming data | Elasticsearch Guide [7.13] | Elastic](https://www.elastic.co/guide/en/elasticsearch/reference/current/transforms.html)

---

<div class="post-metadata">

**Author:** ![theirfan](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/theirfan/32/89339_2.png) [@theirfan](https://discuss.elastic.co/u/theirfan)\
**Post date:** [June 10, 2021, 3:52am UTC](https://discuss.elastic.co/t/painless-script-to-calculate-elapsed-time-between-two-fields/275133/12 "2021-06-10T03:52:52Z")

</div>

Thanks for the suggestion.

The example given in docs for transforming is grouping. In my case, i want to merge or group `up` and `down` (which are timestamps) into a single document.

---

<div class="post-metadata">

**Author:** ![flash1293](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/flash1293/32/41227_2.png) [@flash1293](https://discuss.elastic.co/u/flash1293)\
**Post date:** [June 10, 2021, 10:04am UTC](https://discuss.elastic.co/t/painless-script-to-calculate-elapsed-time-between-two-fields/275133/13 "2021-06-10T10:04:56Z")

</div>

Right, good point. Is there any field in your documents that could be used for grouping?

---

<div class="post-metadata">

**Author:** ![theirfan](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/theirfan/32/89339_2.png) [@theirfan](https://discuss.elastic.co/u/theirfan)\
**Post date:** [June 10, 2021, 1:19pm UTC](https://discuss.elastic.co/t/painless-script-to-calculate-elapsed-time-between-two-fields/275133/14 "2021-06-10T13:19:05Z")

</div>

Unfortunately, for this particular index,I don't.

I still trying for using elapsed filter on these but the problem is with the worker 1 in LS. Using elapsed with worker 1 is making the elapsed join start and end tags from different files of different directories. Which is giving me false time differences between start and end.

Is there a way I can make logstash reset the elapsed filter once the file is done reading and start on a new file, without joining the start tag from previous dir/file ?

If we can do this, then I could get the desired differences. Looks for of a LS issue than kibana to me!

---

<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:** [July 8, 2021, 1:19pm UTC](https://discuss.elastic.co/t/painless-script-to-calculate-elapsed-time-between-two-fields/275133/15 "2021-07-08T13:19:48Z")

</div>

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