Computing number of days between 2 dates in a query


(Vincent Massol) #1

Hi guys,

I'm using ES to receive pings from clients with the following data sent
(see http://design.xwiki.org/xwiki/bin/view/Proposal/ActiveInstalls2 for
more context if you need the full picture):

curl -XPOST "http://localhost:9200/installs/install?timestamp=2014-02-20"
-d'
{
"formatVersion" : "2.0",
"instanceId" : "abc",
"distributionId" : "org.xwiki.enterprise:xwiki-enterprise-web",
"distributionVersion" : "6.0-milestone-1"
}'

I'd like to compute the average elapsed days clients take to upgrade their
versions (ie when distributionVersion changes). So far I've done this:

{
"aggs": {
"instanceId_count" : {
"terms" : { "field" : "instanceId" },
"aggs" : {
"versions" : {
"terms" : { "field" : "distributionVersion" },
"aggs" : {
"date_stats" : {
"stats" : { "field" : "_timestamp" }
}
}
}
}
}
}
}

Now this returns data such as:

"aggregations": {
"instanceId_count": {
"buckets": [
{
"key": "abc",
"doc_count": 2,
"versions": {
"buckets": [
{
"key": "6.0-milestone-1",
"doc_count": 1,
"date_stats": {
"count": 1,
"min": 1392854400000,
"max": 1392854400000,
"avg": 1392854400000,
"sum": 1392854400000
}
},
...

The next step is to compute the difference between date_stats.max and
date_stats.min to get the number of days. And then I'd need to do a global
average on the resulting days. Is it possible? :slight_smile:

Is it possible to use scripting to reference the result of an upstream
aggregation?

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.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/e29004f0-8595-4e9f-a2d2-876182cc9ca0%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.


(system) #2