# Elasticsearch & kibana 6.4: Painless scripting is not providing result while doing substraction between two timestamps, which are in string

**URL:** <https://discuss.elastic.co/t/elasticsearch-kibana-6-4-painless-scripting-is-not-providing-result-while-doing-substraction-between-two-timestamps-which-are-in-string/162862>\
**Category:** Kibana\
**Created:** [January 3, 2019, 6:19pm UTC](https://discuss.elastic.co/t/elasticsearch-kibana-6-4-painless-scripting-is-not-providing-result-while-doing-substraction-between-two-timestamps-which-are-in-string/162862 "2019-01-03T18:19:28Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![kishore419](https://avatars.discourse-cdn.com/v4/letter/k/b4bc9f/32.png) [@kishore419](https://discuss.elastic.co/u/kishore419)\
**Post date:** [January 3, 2019, 6:19pm UTC](https://discuss.elastic.co/t/elasticsearch-kibana-6-4-painless-scripting-is-not-providing-result-while-doing-substraction-between-two-timestamps-which-are-in-string/162862/1 "2019-01-03T18:19:28Z")

</div>

Hello friends,  
i have been using elasticsearch and kibana 6.4 version, forwarded couple of events to elasticsearch using filebeat.  
sample event:  
"hostname":"QA-Lab-01"  
"repliedTime": "2018-12-27T10:58:28.423-05:00",  
"receivedTime": "2018-12-27T10:58:27.168-05:00"

in the above event, repliedTime & receivedTime indexed as string(FiledType) instead of Dates, hence used following script field to get the time difference between both..

 ![image](https://us1.discourse-cdn.com/elastic/original/3X/3/7/3789967b4cc19a47391fc2793fcce4ea24544921.png)

Script field name: **totalRequestTime**  
Query: (new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.S").parse(doc['repliedTime'].value).getTime()) - (new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.S").parse(doc['receivedTime'].value).getTime())

repliedTime & receivedTime Both the field type is **String** and both are searchable and Aggregatable.  
i don't found repliedTime.Keyword & receivedTime.keyword field types in filelds tab.

But unfortunately, above query is not providing any matching result and observed shard failure messages in kibana UI.

when i executed the similar query from dev-tools, it is giving accurate result.  
i am wondering where is the issue and how to fix this issue. Due to this issue i could not able to create visualization on time difference.  
can any one please help me

GET myindex-\*/\_search  
{  
"query":{  
"exists": { "field": "repliedTime" }  
},  
"script\_fields" : {  
"totalRequestTime" : {  
"script" : {  
"inline": "((new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.S").parse(doc['repliedTime'].value).getTime()) - (new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.S").parse(doc['receivedTime'].value).getTime())) ;"  
}  
}  
}

---

<div class="post-metadata">

**Author:** ![Igor\_Motov](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/igor_motov/32/45193_2.png) [@Igor\_Motov](https://discuss.elastic.co/u/Igor_Motov)\
**Post date:** [January 3, 2019, 11:29pm UTC](https://discuss.elastic.co/t/elasticsearch-kibana-6-4-painless-scripting-is-not-providing-result-while-doing-substraction-between-two-timestamps-which-are-in-string/162862/2 "2019-01-03T23:29:34Z")

</div>

> [@kishore419](#):
>
> when i executed the similar query from dev-tools, it is giving accurate result.

In this case this is probably a good topic for kibana category. I am going to move it there.

---

<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:** [January 31, 2019, 11:29pm UTC](https://discuss.elastic.co/t/elasticsearch-kibana-6-4-painless-scripting-is-not-providing-result-while-doing-substraction-between-two-timestamps-which-are-in-string/162862/3 "2019-01-31T23:29:35Z")

</div>

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