# Using Top Hits aggregation for calculating time duration in each grouped field

**URL:** <https://discuss.elastic.co/t/using-top-hits-aggregation-for-calculating-time-duration-in-each-grouped-field/95166>\
**Category:** Elasticsearch\
**Created:** [July 31, 2017, 10:20am UTC](https://discuss.elastic.co/t/using-top-hits-aggregation-for-calculating-time-duration-in-each-grouped-field/95166 "2017-07-31T10:20:44Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![Jenny\_Hsiao](https://avatars.discourse-cdn.com/v4/letter/j/ecccb3/32.png) [@Jenny\_Hsiao](https://discuss.elastic.co/u/Jenny_Hsiao)\
**Post date:** [July 31, 2017, 10:20am UTC](https://discuss.elastic.co/t/using-top-hits-aggregation-for-calculating-time-duration-in-each-grouped-field/95166/1 "2017-07-31T10:20:44Z")

</div>

I am trying to get time duration between each grouped field.

Example:

My document is shown below:

```auto
{
"timestamp":"2017-07-31T15:05:04.563Z",
"session_id":"1",
"user_id":"jenny"
}

```

I want to get time duration in each session\_id.

Moreover, I got a reference of searching solution from stackoverflow([http://bit.ly/2vaBHHj](http://bit.ly/2vaBHHj)) shown below:

```auto
{
"aggs": {
  "group_by_uid": {
     "terms": {
        "field": "user_id"
     },
     "aggs": {
        "group_by_sid": {
           "terms": {
              "field": "session_id"
           },
           "aggs": {
              "session_start": {
                 "top_hits": {
                    "size": 1,
                    "sort": [{ "timestamp": { "order": "asc" } }]
                 }
              },
              "session_end": {
                 "top_hits": {
                    "size": 1,
                    "sort": [{ "timestamp": { "order": "desc" } }]
                 }
              }
           }
        }
     }
  }
}
}

```

My question is that how can I get the time duration (session\_end.timestamp - session\_start.timestamp )?  
Can anybody help me ? Thank you!

---

<div class="post-metadata">

**Author:** ![Mark\_Harwood](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mark_harwood/32/10538_2.png) [@Mark\_Harwood](https://discuss.elastic.co/u/Mark_Harwood)\
**Post date:** [July 31, 2017, 10:30am UTC](https://discuss.elastic.co/t/using-top-hits-aggregation-for-calculating-time-duration-in-each-grouped-field/95166/2 "2017-07-31T10:30:52Z")

</div>

Attempting this sort of computation on a large event store with many unique session IDs is not advised.  
See [entity centric indexing](https://www.youtube.com/watch?v=yBf7oeJKH2Y)

---

<div class="post-metadata">

**Author:** ![Ivan](https://avatars.discourse-cdn.com/v4/letter/i/df788c/32.png) [@Ivan](https://discuss.elastic.co/u/Ivan)\
**Post date:** [July 31, 2017, 6:29pm UTC](https://discuss.elastic.co/t/using-top-hits-aggregation-for-calculating-time-duration-in-each-grouped-field/95166/3 "2017-07-31T18:29:44Z")

</div>

You can simplify things by using a stats aggregation on the inner timestamp  
field. You will still need to do the final calculation on the client side,  
but I rather have such logic outside of the database anyways.

[https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-metrics-stats-aggregation.html](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-metrics-stats-aggregation.html)

---

<div class="post-metadata">

**Author:** ![Jenny\_Hsiao](https://avatars.discourse-cdn.com/v4/letter/j/ecccb3/32.png) [@Jenny\_Hsiao](https://discuss.elastic.co/u/Jenny_Hsiao)\
**Post date:** [August 1, 2017, 3:35am UTC](https://discuss.elastic.co/t/using-top-hits-aggregation-for-calculating-time-duration-in-each-grouped-field/95166/4 "2017-08-01T03:35:43Z")

</div>

Thanks for your sharing!

---

<div class="post-metadata">

**Author:** ![Jenny\_Hsiao](https://avatars.discourse-cdn.com/v4/letter/j/ecccb3/32.png) [@Jenny\_Hsiao](https://discuss.elastic.co/u/Jenny_Hsiao)\
**Post date:** [August 1, 2017, 5:42am UTC](https://discuss.elastic.co/t/using-top-hits-aggregation-for-calculating-time-duration-in-each-grouped-field/95166/5 "2017-08-01T05:42:34Z")

</div>

Hi Ivan,  
Do you know is there a way to calculate time duration in this query? How can I pick session\_start.timestamp and session\_end.timestamp to calculate their difference?  
Or I can only do calculation after the getting the result.

---

<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:** [August 29, 2017, 5:42am UTC](https://discuss.elastic.co/t/using-top-hits-aggregation-for-calculating-time-duration-in-each-grouped-field/95166/6 "2017-08-29T05:42:45Z")

</div>

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