# Calculate the time difference between consecutive documents

**URL:** <https://discuss.elastic.co/t/calculate-the-time-difference-between-consecutive-documents/128282>\
**Category:** Elasticsearch\
**Created:** [April 17, 2018, 4:40am UTC](https://discuss.elastic.co/t/calculate-the-time-difference-between-consecutive-documents/128282 "2018-04-17T04:40:44Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![vinayakfutak](https://avatars.discourse-cdn.com/v4/letter/v/bb73d2/32.png) [@vinayakfutak](https://discuss.elastic.co/u/vinayakfutak)\
**Post date:** [April 17, 2018, 4:40am UTC](https://discuss.elastic.co/t/calculate-the-time-difference-between-consecutive-documents/128282/1 "2018-04-17T04:40:44Z")

</div>

Can I calculate time difference between consecutive document in elasticsearch?  
Suppose there are 3 records,the timestamp of first record is 10:45:00 and timestamp of second record is 10:47:00 and timestamp of next record is 10:50:00;the time difference between first and second record is of 2 minutes and time difference between second and third record is of 3 minutes.  
It is possible to calculate time difference between consecutive documents?

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [April 17, 2018, 5:02am UTC](https://discuss.elastic.co/t/calculate-the-time-difference-between-consecutive-documents/128282/2 "2018-04-17T05:02:57Z")

</div>

I don't think you can. This is something you typically need to solve at index time.  
For example, have a look at the aggregate filter in Logstash.

---

<div class="post-metadata">

**Author:** ![vinayakfutak](https://avatars.discourse-cdn.com/v4/letter/v/bb73d2/32.png) [@vinayakfutak](https://discuss.elastic.co/u/vinayakfutak)\
**Post date:** [April 17, 2018, 6:05am UTC](https://discuss.elastic.co/t/calculate-the-time-difference-between-consecutive-documents/128282/3 "2018-04-17T06:05:45Z")

</div>

Thanks for your heads up @dadoonet. Whether It is possible to calculate the time difference using derivative or serial differencing aggregation?

---

<div class="post-metadata">

**Author:** ![val](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/val/32/138203_2.png) [@val](https://discuss.elastic.co/u/val)\
**Post date:** [April 17, 2018, 6:25am UTC](https://discuss.elastic.co/t/calculate-the-time-difference-between-consecutive-documents/128282/4 "2018-04-17T06:25:47Z")

</div>

In theory, it should be possible to use a `date_histogram` to aggregate documents at the (milli-)second level and then use a derivative of the timestamp, but in practice, I think performance would suffer a great deal.

---

<div class="post-metadata">

**Author:** ![vinayakfutak](https://avatars.discourse-cdn.com/v4/letter/v/bb73d2/32.png) [@vinayakfutak](https://discuss.elastic.co/u/vinayakfutak)\
**Post date:** [April 17, 2018, 6:31am UTC](https://discuss.elastic.co/t/calculate-the-time-difference-between-consecutive-documents/128282/5 "2018-04-17T06:31:42Z")

</div>

thanx @val..I tried same solution and as you said performance wise it is not good practice.

---

<div class="post-metadata">

**Author:** ![val](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/val/32/138203_2.png) [@val](https://discuss.elastic.co/u/val)\
**Post date:** [April 17, 2018, 6:45am UTC](https://discuss.elastic.co/t/calculate-the-time-difference-between-consecutive-documents/128282/6 "2018-04-17T06:45:36Z")

</div>

Indeed, as suggested by @dadoonet and [myself](https://stackoverflow.com/questions/49870087/can-i-calculate-time-difference-between-consecutive-document-in-elasticsearch), you should capture that information at indexing time.

---

<div class="post-metadata">

**Author:** ![vinayakfutak](https://avatars.discourse-cdn.com/v4/letter/v/bb73d2/32.png) [@vinayakfutak](https://discuss.elastic.co/u/vinayakfutak)\
**Post date:** [April 23, 2018, 5:26am UTC](https://discuss.elastic.co/t/calculate-the-time-difference-between-consecutive-documents/128282/7 "2018-04-23T05:26:25Z")

</div>

My data is coming from stream and I am directly storing it into elasticsearch index..can logstash aggregate filter work with stream of data?

---

<div class="post-metadata">

**Author:** ![MariumHassan](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mariumhassan/32/30321_2.png) [@MariumHassan](https://discuss.elastic.co/u/MariumHassan)\
**Post date:** [April 23, 2018, 6:04am UTC](https://discuss.elastic.co/t/calculate-the-time-difference-between-consecutive-documents/128282/8 "2018-04-23T06:04:14Z")

</div>

Hi,  
I did the almost same thing using Ruby in my logstash configuration file. I have used ruby map to store timestamp of the first record, and I calculate the difference when second record is received using Time.parse() function of ruby, and then I finally store the difference in the second record.  
This will continue but you need to make sure that your logs are read in a sequence and no multi threading etc otherwise you might get the wrong results.

```auto
map['timeDifference']= (Time.parse(event.get('logTimestamp')).to_f - Time.parse(map['previousTime']).to_f).round(4);
event.set('timeDifference', map['timeDifference']);
map['previousTime'] = event.get('logTimestamp');

```

---

<div class="post-metadata">

**Author:** ![vinayakfutak](https://avatars.discourse-cdn.com/v4/letter/v/bb73d2/32.png) [@vinayakfutak](https://discuss.elastic.co/u/vinayakfutak)\
**Post date:** [April 23, 2018, 6:30am UTC](https://discuss.elastic.co/t/calculate-the-time-difference-between-consecutive-documents/128282/9 "2018-04-23T06:30:05Z")

</div>

thanks @MariumHassan,I am new to logstash so can you send me script?

---

<div class="post-metadata">

**Author:** ![MariumHassan](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mariumhassan/32/30321_2.png) [@MariumHassan](https://discuss.elastic.co/u/MariumHassan)\
**Post date:** [April 23, 2018, 7:31am UTC](https://discuss.elastic.co/t/calculate-the-time-difference-between-consecutive-documents/128282/10 "2018-04-23T07:31:22Z")

</div>

Sorry, I don't have the relevant script available at the moment. You can take help from [configuration examples](https://www.elastic.co/guide/en/logstash/current/config-examples.html). Modify it according to your need and add the code below in the filter part after the required changes

```auto
ruby
{
  init => "@@map = {}"
  code => "map['timeDifference']= (Time.parse(event.get('logTimestamp')).to_f - Time.parse(map['previousTime']).to_f).round(4);
           event.set('timeDifference', map['timeDifference']);
           map['previousTime'] = event.get('logTimestamp');"
}

```

You might need to update the grok pattern according to your logs.

---

<div class="post-metadata">

**Author:** ![saramali](https://avatars.discourse-cdn.com/v4/letter/s/da6949/32.png) [@saramali](https://discuss.elastic.co/u/saramali)\
**Post date:** [April 23, 2018, 7:32am UTC](https://discuss.elastic.co/t/calculate-the-time-difference-between-consecutive-documents/128282/11 "2018-04-23T07:32:37Z")

</div>

Great job @MariumHassan

---

<div class="post-metadata">

**Author:** ![vinayakfutak](https://avatars.discourse-cdn.com/v4/letter/v/bb73d2/32.png) [@vinayakfutak](https://discuss.elastic.co/u/vinayakfutak)\
**Post date:** [April 25, 2018, 5:27am UTC](https://discuss.elastic.co/t/calculate-the-time-difference-between-consecutive-documents/128282/12 "2018-04-25T05:27:31Z")

</div>

hey @MariumHassan. Can I take input from elasticsearch index and apply ruby filter on that??

---

<div class="post-metadata">

**Author:** ![MariumHassan](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mariumhassan/32/30321_2.png) [@MariumHassan](https://discuss.elastic.co/u/MariumHassan)\
**Post date:** [April 25, 2018, 5:50am UTC](https://discuss.elastic.co/t/calculate-the-time-difference-between-consecutive-documents/128282/13 "2018-04-25T05:50:17Z")

</div>

Hi,  
You can query data from elasticsearch and process it using any language you prefer. However, updating that data after processing it in this way might be an issue here. I think you will need to update it by query or you can simply get/store document id of the document you want to update and use that to update the document later.  
This is helpful: [Logstash Update a document in elasticsearch](https://discuss.elastic.co/t/logstash-update-a-document-in-elasticsearch/74039/5)

---

<div class="post-metadata">

**Author:** ![vinayakfutak](https://avatars.discourse-cdn.com/v4/letter/v/bb73d2/32.png) [@vinayakfutak](https://discuss.elastic.co/u/vinayakfutak)\
**Post date:** [April 25, 2018, 9:58am UTC](https://discuss.elastic.co/t/calculate-the-time-difference-between-consecutive-documents/128282/14 "2018-04-25T09:58:24Z")

</div>

HI @MariumHassan this is my script

```
input {
elasticsearch {
 hosts => "http://192.168.55.213:9200"
 index => "test_index"
}
}
filter {
aggregate {
task_id => "%{logTimestamp}"
code => "
     map['logTimestamp'] = event.get('logTimestamp');
     map['timeDifference']= (Time.parse(event.get('logTimestamp')).to_f - 
     Time.parse(map['previousTime']).to_f).round(4);
     map['previousTime'] = event.get('logTimestamp');
 
"
}
}
output {
elasticsearch {
document_id => "%{logTimestamp}"
document_type => "test_index1"
index => "test_index1"
codec => "json"
hosts => ["192.168.55.213:9200"]
}
}

```

When I am trying to execute this script it's giving error as

Aggregate exception occurred {:error=\>#\<TypeError: can't dup NilClass\>, :code=\>"\n\t map['logTimestamp'] = event.get('logTimestamp');\n map['timeDifference']= (Time.parse(event.get('logTimestamp')).to\_f - Time.parse(map['previousTime']).to\_f).round(4);\n\tmap['previousTime'] = event.get('logTimestamp');\n\t \n ", :map=\>{"logTimestamp"=\>"2018-02-15T08:40:10Z"}, :event\_data=\>{"logTimestamp"=\>"2018-02-15T08:40:10Z", "@timestamp"=\>2018-04-25T09:47:38.312Z, "@version"=\>"1"}}

Can U suggest what changes I have to done to run script successfully?

---

<div class="post-metadata">

**Author:** ![vinayakfutak](https://avatars.discourse-cdn.com/v4/letter/v/bb73d2/32.png) [@vinayakfutak](https://discuss.elastic.co/u/vinayakfutak)\
**Post date:** [April 26, 2018, 8:59am UTC](https://discuss.elastic.co/t/calculate-the-time-difference-between-consecutive-documents/128282/15 "2018-04-26T08:59:11Z")

</div>

Hi @MariumHassan My script is working but I am getting same time in previousTime field and If I do not declare map['previousTime '] at start of the script then it's throwing exception

---

<div class="post-metadata">

**Author:** ![MariumHassan](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mariumhassan/32/30321_2.png) [@MariumHassan](https://discuss.elastic.co/u/MariumHassan)\
**Post date:** [April 26, 2018, 10:12am UTC](https://discuss.elastic.co/t/calculate-the-time-difference-between-consecutive-documents/128282/16 "2018-04-26T10:12:34Z")

</div>

Hi, you need to apply a check. Something like this:

```auto
if map['previousTime'] == nil
   map['previousTime'] = 0;

```

---

<div class="post-metadata">

**Author:** ![vinayakfutak](https://avatars.discourse-cdn.com/v4/letter/v/bb73d2/32.png) [@vinayakfutak](https://discuss.elastic.co/u/vinayakfutak)\
**Post date:** [April 27, 2018, 10:29am UTC](https://discuss.elastic.co/t/calculate-the-time-difference-between-consecutive-documents/128282/17 "2018-04-27T10:29:34Z")

</div>

It's working @MariumHassan...but sometimes it's not giving proper results and when I am adding query in input to store result in ascending order,that query is not working at all (Record is not inserted in ascending order)

This is my updated config file

input {  
elasticsearch {  
hosts =\> "[http://192.168.55.213:9200](http://192.168.55.213:9200)"  
index =\> "test\_index"  
type =\> "test\_index"  
query =\> '{"sort": [{"logTimestamp": {"order": "asc"}}],"query": {"match\_all": {}}}'  
}  
}  
filter {  
ruby {  
init =\> "@@map = {}"  
code =\> "  
@@map['previousTime'] = event.get('logTimestamp') if @@map['previousTime'].nil?  
@@map['timeDifference']= (Time.parse(event.get('logTimestamp')).to\_f -  
Time.parse(@@map['previousTime']).to\_f);  
event.set('timeDifference',@@map['timeDifference']);  
event.set('previousTime',@@map['previousTime'])  
@@map['previousTime']=event.get('logTimestamp');  
"  
}  
mutate {  
remove\_field =\> ["@version","@timestamp"]  
}

}  
output {  
elasticsearch {  
document\_type =\> "test\_index3"  
index =\> "test\_index3"  
codec =\> "json"  
hosts =\> ["192.168.55.213:9200"]  
}  
}

---

<div class="post-metadata">

**Author:** ![MariumHassan](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mariumhassan/32/30321_2.png) [@MariumHassan](https://discuss.elastic.co/u/MariumHassan)\
**Post date:** [April 27, 2018, 10:59am UTC](https://discuss.elastic.co/t/calculate-the-time-difference-between-consecutive-documents/128282/18 "2018-04-27T10:59:05Z")

</div>

Hi, I tried this in my kibana console:

```auto
GET _search
{
  "sort" : [
       {"logTimestamp": {"order": "asc"}}
       ],
  "query": {
    "match_all": {}
  }
}

```

It works fine for me. You get sorted input from elasticsearch and calculate the time difference between two consecutive documents. Now, you should add that time difference field in the current document by updating the existing one instead of inserting it again. Please check: [https://www.elastic.co/guide/en/logstash/current/plugins-outputs-elasticsearch.html#plugins-outputs-elasticsearch-action](https://www.elastic.co/guide/en/logstash/current/plugins-outputs-elasticsearch.html#plugins-outputs-elasticsearch-action)

---

<div class="post-metadata">

**Author:** ![vinayakfutak](https://avatars.discourse-cdn.com/v4/letter/v/bb73d2/32.png) [@vinayakfutak](https://discuss.elastic.co/u/vinayakfutak)\
**Post date:** [April 27, 2018, 11:29am UTC](https://discuss.elastic.co/t/calculate-the-time-difference-between-consecutive-documents/128282/19 "2018-04-27T11:29:40Z")

</div>

It is asking for document\_id to update document.What I have to provide in document\_id?

---

<div class="post-metadata">

**Author:** ![vinayakfutak](https://avatars.discourse-cdn.com/v4/letter/v/bb73d2/32.png) [@vinayakfutak](https://discuss.elastic.co/u/vinayakfutak)\
**Post date:** [April 27, 2018, 12:28pm UTC](https://discuss.elastic.co/t/calculate-the-time-difference-between-consecutive-documents/128282/20 "2018-04-27T12:28:39Z")

</div>

i tried with multiple options like  
document\_id =\> "%{Doc\_id}"  
document\_id =\>[@metadata][\_id]  
document\_id =\>"%{uid}"

but it's not working.I want to override same record with added field time\_difference

[Next page](https://discuss.elastic.co/t/calculate-the-time-difference-between-consecutive-documents/128282.md?page=2)