# How to get difference of min timestamp and max timestamp?

**URL:** https://discuss.elastic.co/t/how-to-get-difference-of-min-timestamp-and-max-timestamp/199589
**Category:** Kibana
**Created:** [September 16, 2019, 7:12am UTC](https://discuss.elastic.co/t/how-to-get-difference-of-min-timestamp-and-max-timestamp/199589 "2019-09-16T07:12:20Z")
**Posts on this page:** 4
**Page:** 1

<div class="post-metadata">

### Author: ![samath\_kumar](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/samath_kumar/32/54248_2.png) [@samath\_kumar](https://discuss.elastic.co/u/samath_kumar)
#### Post date: [September 16, 2019, 7:12am UTC](https://discuss.elastic.co/t/how-to-get-difference-of-min-timestamp-and-max-timestamp/199589/1 "2019-09-16T07:12:20Z")

</div>

I am new to kibana and elastic search, i want a metrics for average , total , longest of the timespans generated by taking difference of max and min timestamp fields. First I got min and max timestamps from the visualization of data table in Kibana ,in which 2 metrics (min @timestamp, max @timestamp) are there having the aggregation of top hit and size 1 respectively, one is ascending (min timestamp) and the other descending(max timestamp). I also have a bucket with split rows which is grouping my data by the field contactId

 ![ElasticTimeStamp](https://us1.discourse-cdn.com/elastic/original/3X/6/a/6a658b7f3582b9cd958ed2985fca63efed1f34da.png) .

My log data is like

{  
"contactId":"XXXXX",  
"TimeStamp":"XXXXX"  
}

---

<div class="post-metadata">

### Author: ![Brandon\_Kobel](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/brandon_kobel/32/14829_2.png) [@Brandon\_Kobel](https://discuss.elastic.co/u/Brandon_Kobel)
#### Post date: [September 16, 2019, 4:04pm UTC](https://discuss.elastic.co/t/how-to-get-difference-of-min-timestamp-and-max-timestamp/199589/2 "2019-09-16T16:04:26Z")

</div>

Hey @samath_kumar, you can do this using Canvas and a Table using the Elasticsearch SQL data-source with SQL similar to the following:

```auto
SELECT CAST(MAX("@timestamp") AS DOUBLE) - CAST(MIN("@timestamp") AS DOUBLE) FROM "bar" GROUP BY "contactId"

```

 ![canvas-sql](https://us1.discourse-cdn.com/elastic/original/3X/b/f/bf9f7084a1f747eb328a285ddfa2e2622af3f041.gif)

---

<div class="post-metadata">

### Author: ![samath\_kumar](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/samath_kumar/32/54248_2.png) [@samath\_kumar](https://discuss.elastic.co/u/samath_kumar)
#### Post date: [September 17, 2019, 8:58am UTC](https://discuss.elastic.co/t/how-to-get-difference-of-min-timestamp-and-max-timestamp/199589/3 "2019-09-17T08:58:05Z")

</div>

Glad it helped...Thank you @Brandon_Kobel

---

<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: [October 15, 2019, 8:58am UTC](https://discuss.elastic.co/t/how-to-get-difference-of-min-timestamp-and-max-timestamp/199589/4 "2019-10-15T08:58:09Z")

</div>

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