# Mixing Range aggregation and computing days between 2 dates?

**URL:** <https://discuss.elastic.co/t/mixing-range-aggregation-and-computing-days-between-2-dates/16821>\
**Category:** Elasticsearch\
**Created:** [April 4, 2014, 1:45pm UTC](https://discuss.elastic.co/t/mixing-range-aggregation-and-computing-days-between-2-dates/16821 "2014-04-04T13:45:15Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![Vincent\_Massol](https://avatars.discourse-cdn.com/v4/letter/v/ac91a4/32.png) [@Vincent\_Massol](https://discuss.elastic.co/u/Vincent_Massol)\
**Post date:** [April 4, 2014, 1:45pm UTC](https://discuss.elastic.co/t/mixing-range-aggregation-and-computing-days-between-2-dates/16821/1 "2014-04-04T13:45:15Z")

</div>

Hi ES experts,

I have instances (XWiki instances to be precise) sending pings every day to  
an ES server (the model is very simple: one \_timestamp field for the date  
and an "instanceId" field for uniquely identifying the instance sending the  
ping).

I'd like to find how to write a query that shows the average number of days  
each instance has been sending pings, by displaying counts for instances  
having their last ping date - first ping date \< 1 day, between 1 day and 7  
days, between 7 days and 365 days and over 365 days.

I've tried several aggregations but I can't find one that will work out.

I'd like something like this:

curl -XGET  
"[http://localhost:9200/installs/install/\_search?search\_type=count&pretty=1](http://localhost:9200/installs/install/_search?search_type=count&pretty=1)"  
-d'  
{  
"aggs": {  
"installs\_by\_range" : {  
"range" : {  
"script" : "\<compute max\_date - min\_date in days here\>",  
"ranges" : [  
{ "to" : 2 },  
{ "from" : 2, "to" : 30 },  
{ "from" : 30, "to" : 365 },  
{ "from" : 365 }  
]  
},  
"aggs" : {  
"instanceIds" : {  
"terms" : { "field" : "instanceId" },  
"aggs" : {  
"min\_date" : { "min" : { "field" : "\_timestamp" } },  
"max\_date" : { "max" : { "field" : "\_timestamp" } }  
}  
}  
}  
}  
}  
}'

Here's a script to generate some minimal data:  
[http://design.xwiki.org/xwiki/bin/view/Proposal/ActiveInstalls2#HData](http://design.xwiki.org/xwiki/bin/view/Proposal/ActiveInstalls2#HData)

--  
You received this message because you are subscribed to the Google Groups "elasticsearch" group.  
To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com).  
To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/0541a994-e254-4056-b294-20a036b0616b%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/0541a994-e254-4056-b294-20a036b0616b%40googlegroups.com).  
For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).

---

<div class="post-metadata">

**Author:** ![Vincent\_Massol](https://avatars.discourse-cdn.com/v4/letter/v/ac91a4/32.png) [@Vincent\_Massol](https://discuss.elastic.co/u/Vincent_Massol)\
**Post date:** [April 4, 2014, 1:46pm UTC](https://discuss.elastic.co/t/mixing-range-aggregation-and-computing-days-between-2-dates/16821/2 "2014-04-04T13:46:19Z")

</div>

On Friday, April 4, 2014 3:45:15 PM UTC+2, Vincent Massol wrote:

> Hi ES experts,
> 
> I have instances (XWiki instances to be precise) sending pings every day  
> to an ES server (the model is very simple: one \_timestamp field for the  
> date and an "instanceId" field for uniquely identifying the instance  
> sending the ping).
> 
> I'd like to find how to write a query that shows the average number of  
> days each instance has been sending pings, by displaying counts for  
> instances having their last ping date - first ping date \< 1 day, between 1  
> day and 7 days, between 7 days and 365 days and over 365 days.
> 
> I've tried several aggregations but I can't find one that will work out.
> 
> I'd like something like this:
> 
> curl -XGET "  
> [http://localhost:9200/installs/install/\_search?search\_type=count&pretty=1](http://localhost:9200/installs/install/_search?search_type=count&pretty=1)"  
> -d'  
> {  
> "aggs": {  
> "installs\_by\_range" : {  
> "range" : {  
> "script" : "\<compute max\_date - min\_date in days here\>",  
> "ranges" : [  
> { "to" : 2 },  
> { "from" : 2, "to" : 30 },  
> { "from" : 30, "to" : 365 },  
> { "from" : 365 }  
> ]  
> },  
> "aggs" : {  
> "instanceIds" : {  
> "terms" : { "field" : "instanceId" },  
> "aggs" : {  
> "min\_date" : { "min" : { "field" : "\_timestamp" }  
> },  
> "max\_date" : { "max" : { "field" : "\_timestamp" } }  
> }  
> }  
> }  
> }  
> }  
> }'
> 
> Here's a script to generate some minimal data:  
> [http://design.xwiki.org/xwiki/bin/view/Proposal/ActiveInstalls2#HData](http://design.xwiki.org/xwiki/bin/view/Proposal/ActiveInstalls2#HData)

oops forget to finish my email 😉

---- end of previous email ----

Thanks a lot for any pointer!  
-Vincent

--  
You received this message because you are subscribed to the Google Groups "elasticsearch" group.  
To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com).  
To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/e809837a-0e14-400b-8ed8-a652616dcab8%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/e809837a-0e14-400b-8ed8-a652616dcab8%40googlegroups.com).  
For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).

---

<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:** [July 6, 2017, 1:38am UTC](https://discuss.elastic.co/t/mixing-range-aggregation-and-computing-days-between-2-dates/16821/3 "2017-07-06T01:38:08Z")

</div>