# How to find the time difference between two documents (rows in sql) in an index (table in sql) with same Id

**URL:** <https://discuss.elastic.co/t/how-to-find-the-time-difference-between-two-documents-rows-in-sql-in-an-index-table-in-sql-with-same-id/268782>\
**Category:** Elasticsearch\
**Tags:** elastic-stack-sql\
**Created:** [March 30, 2021, 11:37am UTC](https://discuss.elastic.co/t/how-to-find-the-time-difference-between-two-documents-rows-in-sql-in-an-index-table-in-sql-with-same-id/268782 "2021-03-30T11:37:53Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![imganeshr](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/imganeshr/32/80029_2.png) [@imganeshr](https://discuss.elastic.co/u/imganeshr)\
**Post date:** [March 30, 2021, 11:37am UTC](https://discuss.elastic.co/t/how-to-find-the-time-difference-between-two-documents-rows-in-sql-in-an-index-table-in-sql-with-same-id/268782/1 "2021-03-30T11:37:53Z")

</div>

Hello Everyone,  
I am new to Elastic Search and Kibana Query Language.  
I would like to find the time difference between the two documents which have the id as common between them as like below.  
TimeStamp TRACE ID  
2021-03-29 20:45:15.748 START 1234  
2021-03-29 20:46:15.148 END 1234

I have given my tries with SQL Join and LAG and LEAD functions. But unfortunately, ES Driver doesn't support these as of now.

It would be really helpful if someone can help with either SQL or other search mechanisms in ELK.

Regards,  
Ganesh Kumar

---

<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:** [March 30, 2021, 3:33pm UTC](https://discuss.elastic.co/t/how-to-find-the-time-difference-between-two-documents-rows-in-sql-in-an-index-table-in-sql-with-same-id/268782/2 "2021-03-30T15:33:47Z")

</div>

You can't really do that in a NoSQL world. It's better to do that kind of join at index time.

So wait for the end of an event and compute that at index time.

There's for example this logstash plugin which can help. In case you're using logstash.

> **[Aggregate filter plugin | Logstash Reference \[7.12\] | Elastic](https://www.elastic.co/guide/en/logstash/current/plugins-filters-aggregate.html)**

---

<div class="post-metadata">

**Author:** ![imganeshr](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/imganeshr/32/80029_2.png) [@imganeshr](https://discuss.elastic.co/u/imganeshr)\
**Post date:** [March 30, 2021, 5:08pm UTC](https://discuss.elastic.co/t/how-to-find-the-time-difference-between-two-documents-rows-in-sql-in-an-index-table-in-sql-with-same-id/268782/3 "2021-03-30T17:08:27Z")

</div>

Hi @dadoonet ,

I am not using logstash.

Can you please let me know how to define this join while creating the index.

Regards,  
Ganesh Kumar

---

<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:** [March 30, 2021, 7:03pm UTC](https://discuss.elastic.co/t/how-to-find-the-time-difference-between-two-documents-rows-in-sql-in-an-index-table-in-sql-with-same-id/268782/4 "2021-03-30T19:03:57Z")

</div>

> [@imganeshr](#):
>
> Can you please let me know how to define this join while creating the index.

Let's say you have an event like:

```
{
  "id": "abc",
  "type": "start",
  "date": "date1"
}

```

And then another like:

```
{
  "id": "abc",
  "type": "stop",
  "date": "date2"
}

```

Compute the duration xyz=date2-date1 and store in elasticsearch:

```
POST /events/_doc
{
  "id": "abc",
  "date_start": "date1",
  "date_end": "date2",
  "duration": xyz
}

```

---

<div class="post-metadata">

**Author:** ![imganeshr](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/imganeshr/32/80029_2.png) [@imganeshr](https://discuss.elastic.co/u/imganeshr)\
**Post date:** [March 31, 2021, 7:12am UTC](https://discuss.elastic.co/t/how-to-find-the-time-difference-between-two-documents-rows-in-sql-in-an-index-table-in-sql-with-same-id/268782/5 "2021-03-31T07:12:13Z")

</div>

Hi @dadoonet ,

Thanks for the Solution. Let me explain my complete scenario here.

I am using a Log4j appender to send my logs from my application (REST API, Which has the JSON Logging capability) to Kibana.  
So basically it's real-time sync between the application and the Elastic Search / Kibana index.  
As mentioned in my post earlier, I have START and END tracepoints and would like to calculate the difference between them to identify the response time.

Please let me know if there are any possibilities to achieve the same.

Thanks,  
Ganesh Kumar

---

<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:** [April 28, 2021, 7:12am UTC](https://discuss.elastic.co/t/how-to-find-the-time-difference-between-two-documents-rows-in-sql-in-an-index-table-in-sql-with-same-id/268782/6 "2021-04-28T07:12:57Z")

</div>

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