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

Hello friends,
i have been using elasticsearch and kibana 6.4 version, forwarded couple of events to elasticsearch using filebeat.
sample event:
"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..

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
"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())) ;"

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

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