# Need help to calculate time delta in ES/Kibana

**URL:** <https://discuss.elastic.co/t/need-help-to-calculate-time-delta-in-es-kibana/85924>\
**Category:** Kibana\
**Tags:** elastic-stack-reporting\
**Created:** [May 16, 2017, 11:50am UTC](https://discuss.elastic.co/t/need-help-to-calculate-time-delta-in-es-kibana/85924 "2017-05-16T11:50:15Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![uttkarshd](https://avatars.discourse-cdn.com/v4/letter/u/ecc23a/32.png) [@uttkarshd](https://discuss.elastic.co/u/uttkarshd)\
**Post date:** [May 16, 2017, 11:50am UTC](https://discuss.elastic.co/t/need-help-to-calculate-time-delta-in-es-kibana/85924/1 "2017-05-16T11:50:15Z")

</div>

Hi,  
I need some help to calculate time difference in one of our indexes. These are application logs, where the application creates and deletes new virtual machines. The log format is as follows:

```
   @timestamp clusterid action
May 15th 2017, 17:20:11.080 cluster1	/action/add
May 15th 2017, 20:21:11.080 cluster2	/action/add
May 15th 2017, 21:25:11.080 cluster1	/action/delete
May 15th 2017, 22:35:11.080 cluster2	/action/delete
May 16th 2017, 10:20:11.080 cluster2	/action/add
May 16th 2017, 15:51:11.080 cluster3	/action/add
May 17th 2017, 20:30:11.080 cluster3	/action/delete

```

Here we need to calculate the time difference(in hourly format) for each clusterid between /action/add and /action/delete. If there is no /action/delete for a specific cluster id, it should be able to show time delta(in hourly format) till now.

I've been researching on this for past couple of days, and written the following query for 'painless' scripted field.

```
"script": {
    "lang": "painless",
    "inline": "
int totallogs= ctx._source.action.size();    
int datetimediff=0;      
int clusterstarttime=0;      
String clusterid;      
int clusterendtime=0;       
for(int i = 0; i < totallogs; ++i)
{ if(ctx._source.url[i]=='/action/add')
{ clusterstarttime=ctx._source.timestamp[i]; clusterid=ctx._source.clusterid[i]; } if(ctx._source.action[i]=='/action/delete' && ctx._source.clusterid == clusterid){ clusterendtime=ctx._source.timestamp[i];          
datetimediff=clusterstarttime - clusterendtime; }      
return datetimediff; } "

```

However, this isn't working at all!  
I've referred to a number of discussions on elastic blog, but nothing has been helpful so far.  
here are a few of the posts I've followed:

> [@Plot latency measure based on two time fields in an index](https://discuss.elastic.co/t/plot-latency-measure-based-on-two-time-fields-in-an-index/73244):
>
> Hi All: I am not sure if this has been asked and answered (my searches have not returned results for this), So here goes : I have an index with a timestamp of when a document was persisted to the index as well as a timestamp of when the document was actually created. I would like to measure the latency as the difference between the two time stamps and then plot it in Kibana, each message has a unique identifier. Is this possible with Timelion or with the Vanilla Kibana. Any help is much …

> [@Kibana 5.1 possible to calculate time difference and place it in a field in kibana at runtime?](https://discuss.elastic.co/t/kibana-5-1-possible-to-calculate-time-difference-and-place-it-in-a-field-in-kibana-at-runtime/73387):
>
> Hi there, I know I can do many cool stuff in logstash, for example calculating time differences and store it in a field. Now my question, is it possible to do such stuff in kibana in realtime too? Say, I have following timestamps in my event @timestamp timestampStart timestampEnd Is it possible to calculate a "virtual" field "timeDiff" in kibana by calculating timestampEnd-timestampStart? I don't need to search by these fields, but aggregating on them would help. Other familiar topic is,…

> [@Calculate time using timestamp!](https://discuss.elastic.co/t/calculate-time-using-timestamp/24123):
>
> Hi, guys!!! I want to calculate how much time a user has spent in the game. I have my documents with fields: timestamp, message. Message field can be 2 different types: "log in" and "log out". Difference in time between "log in" and "log out" is the time my user has spent in my game. Is it possible to calculate the time the user has spent in the game for a month?

> [@How do you use script fields with datetime subtraction](https://discuss.elastic.co/t/how-do-you-use-script-fields-with-datetime-subtraction/39360):
>
> I am trying to use scripted fields to subtrack two datetime fields to determine how long it took for a file to processed. doc.['time\_var1'].value - doc.['time\_var2'].value doc being the index name. I get this error: Visualize: Request to Elasticsearch failed: {"error":"SearchPhaseExecutionException[Failed to execute phase [query], all shards failed; shardFailures {[rtl\_z8lBRrGHGQPtgehi2w] my time\_var1 is something like this: 2016-01-13 16:34:59 the same format for the time\_var2. Is there s…

Most of these posts talk about using a 'scripted field'; but none of them show even a single and simplest example. And as we all know, Kibana and Elasticsearch online documentation is utterly pathetic!

So, can anyone help me with this?

Thanks,  
Utkarsh

---

<div class="post-metadata">

**Author:** ![th\_dap7](https://avatars.discourse-cdn.com/v4/letter/t/a698b9/32.png) [@th\_dap7](https://discuss.elastic.co/u/th_dap7)\
**Post date:** [May 16, 2017, 1:28pm UTC](https://discuss.elastic.co/t/need-help-to-calculate-time-delta-in-es-kibana/85924/2 "2017-05-16T13:28:52Z")

</div>

I've seen in another post that you can extract parts of the date as Integer in a scripted field:  
Integer.parseInt(new SimpleDateFormat("HH").format(new Date(doc["created\_on"].value)))

With that approach it should be possible to do the subtraction

---

<div class="post-metadata">

**Author:** ![uttkarshd](https://avatars.discourse-cdn.com/v4/letter/u/ecc23a/32.png) [@uttkarshd](https://discuss.elastic.co/u/uttkarshd)\
**Post date:** [May 17, 2017, 7:16am UTC](https://discuss.elastic.co/t/need-help-to-calculate-time-delta-in-es-kibana/85924/3 "2017-05-17T07:16:28Z")

</div>

Thanks @th_dap7, could you share that link? if you are able to find it?

---

<div class="post-metadata">

**Author:** ![th\_dap7](https://avatars.discourse-cdn.com/v4/letter/t/a698b9/32.png) [@th\_dap7](https://discuss.elastic.co/u/th_dap7)\
**Post date:** [May 17, 2017, 8:04am UTC](https://discuss.elastic.co/t/need-help-to-calculate-time-delta-in-es-kibana/85924/4 "2017-05-17T08:04:58Z")

</div>

> [@th\_dap7](#):
>
> SimpleDateFormat("HH").format(new Date(doc["created\_on"].value

Sure: [https://discuss.elastic.co/t/extracting-hour-from-date-field-scripted-field/691/6](https://discuss.elastic.co/t/extracting-hour-from-date-field-scripted-field/691/6)

---

<div class="post-metadata">

**Author:** ![uttkarshd](https://avatars.discourse-cdn.com/v4/letter/u/ecc23a/32.png) [@uttkarshd](https://discuss.elastic.co/u/uttkarshd)\
**Post date:** [May 17, 2017, 8:26am UTC](https://discuss.elastic.co/t/need-help-to-calculate-time-delta-in-es-kibana/85924/5 "2017-05-17T08:26:06Z")

</div>

@th_dap7 thanks for sharing the link! I'll try it out!

---

<div class="post-metadata">

**Author:** ![avivc](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/avivc/32/16501_2.png) [@avivc](https://discuss.elastic.co/u/avivc)\
**Post date:** [May 22, 2017, 4:34pm UTC](https://discuss.elastic.co/t/need-help-to-calculate-time-delta-in-es-kibana/85924/6 "2017-05-22T16:34:46Z")

</div>

Hi @uttkarshd  
Did it work? I'm dealing with the same issue  
please share 🙂  
Tnx

---

<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:** [June 19, 2017, 4:34pm UTC](https://discuss.elastic.co/t/need-help-to-calculate-time-delta-in-es-kibana/85924/7 "2017-06-19T16:34:54Z")

</div>

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