Incorrect output result of "aggs" query

Question

I have a query that searches the number of entries in a given datetime window (i.e. between 2017-02-17T15:00:00.000 and 2017-02-17T16:00:00.000). When I execute this query, I get the incorrect result (it's better said that the result is unexpected):

POST /myindex/_search
{
  "size": 0,
  "aggs": {
    "range": {
        "date_range": {
            "field": "Datetime",
            "ranges": [
                { "to": "2017-02-17T16:00:00||-1H/H" }, 
                { "from": "2017-02-17T16:00:00||/H" } 
            ]
        }
    }
}
}

This is the output:

{
  "took": 0,
  "timed_out": false,
  "_shards": {
    "total": 5,
    "successful": 5,
    "failed": 0
  },
  "hits": {
    "total": 11,
    "max_score": 0,
    "hits": []
  },
  "aggregations": {
    "range": {
      "buckets": [
        {
          "key": "*-2017-02-17T15:00:00.000Z",
          "to": 1487343600000,
          "to_as_string": "2017-02-17T15:00:00.000Z",
          "doc_count": 0
        },
        {
          "key": "2017-02-17T16:00:00.000Z-*",
          "from": 1487347200000,
          "from_as_string": "2017-02-17T16:00:00.000Z",
          "doc_count": 0
        }
      ]
    }
  }
}

In myindex I have two entries with the following values of Datetime:

2017-02-17T15:15:00.000Z
2017-02-17T15:02:00.000Z

So, the result should be equal to 2.

I don't understand how to interpret the current output. Which fields defines the number of entries?

UPDATE:

data structure:

PUT /myindex
{
    "mappings": {
      "intensity": {
      "_all": {
        "enabled": false
      },
        "properties": {
          "Country_Id": {
            "type":"keyword"
          },
          "Datetime": {
            "type":"date"
          }
        }
      }
    }
}

sample data:

{
  "took": 0,
  "timed_out": false,
  "_shards": {
    "total": 5,
    "successful": 5,
    "failed": 0
  },
  "hits": {
    "total": 5,
    "max_score": 1,
    "hits": [
      {
        "_index": "myindex",
        "_type": "intensity",
        "_id": "4",
        "_score": 1,
        "_source": {
          "Country_Id": "1",
          "Datetime": "2017-02-18T15:01:00.000Z"
        }
      },
      {
        "_index": "myindex",
        "_type": "intensity",
        "_id": "6",
        "_score": 1,
        "_source": {
          "Country_Id": "1",
          "Datetime": "2017-03-16T16:15:00.000Z"
        }
      },
      {
        "_index": "myindex",
        "_type": "intensity",
        "_id": "1",
        "_score": 1,
        "_source": {
          "Country_Id": "1",
          "Datetime": "2017-02-17T15:15:00.000Z"
        }
      },
      {
        "_index": "myindex",
        "_type": "intensity",
        "_id": "7",
        "_score": 1,
        "_source": {
          "Country_Id": "1",
          "Datetime": "2017-03-16T16:18:00.000Z"
        }
      },
      {
        "_index": "myindex",
        "_type": "intensity",
        "_id": "3",
        "_score": 1,
        "_source": {
          "Country_Id": "1",
          "Datetime": "2017-02-17T15:02:00.000Z"
        }
      }
    ]
  }
}

The answer that I get:

{
  "took": 2,
  "timed_out": false,
  "_shards": {
    "total": 5,
    "successful": 5,
    "failed": 0
  },
  "hits": {
    "total": 11,
    "max_score": 0,
    "hits": []
  },
  "aggregations": {
    "range": {
      "buckets": [
        {
          "key": "2017-02-17T15:00:00.000Z-2017-02-17T16:00:00.000Z",
          "from": 1487343600000,
          "from_as_string": "2017-02-17T15:00:00.000Z",
          "to": 1487347200000,
          "to_as_string": "2017-02-17T16:00:00.000Z",
          "doc_count": 0
        }
      ]
    }
  }
}

If I'm not mistaken, the first range goes to `15:00` and the second range starts from `16:00`, so 15:15 and 15:02 are just in the middle. — Val, Sep 01 '17 at 10:23
@Val: I want to get the number of records between `2017-02-17T15:00:00.000` and `2017-02-17T16:00:00.000`. What is wrong in my query? — Dinosaurius, Sep 01 '17 at 10:25
Why not simply using a `date_histogram` instead with an hourly interval? — Val, Sep 01 '17 at 10:26
@Val: Because I need it for my task. I do not require the whole histogram. — Dinosaurius, Sep 01 '17 at 10:52

Val · Accepted Answer · 2017-09-01T10:48:37.133

2

Your ranges are wrong, do it like this instead

POST /myindex/_search
{
  "size": 0,
  "aggs": {
    "range": {
        "date_range": {
            "field": "Datetime",
            "ranges": [
                { 
                   "from": "2017-02-17T16:00:00Z||-1H/H",
                   "to": "2017-02-17T16:00:00Z||/H" 
                }
            ]
        }
    }
}
}

edited Sep 01 '17 at 10:48

answered Sep 01 '17 at 10:28

Val

207,596
13
358
360

I want to use `-1H`. Is it possible? – Dinosaurius Sep 01 '17 at 10:43
I've modified my answer – Val Sep 01 '17 at 10:46
Ok, thank you. I check it and I get `"doc_count": 0`. Cannot understand why does it happen. By the way, you miss comma at the end of "from". – Dinosaurius Sep 01 '17 at 10:47
I tested your last query, but again the answer is `doc_count` equal to 0. Please see my update. I posted some data. – Dinosaurius Sep 01 '17 at 10:54
Can you also post the answer you get? – Val Sep 01 '17 at 10:55
Please see my update. I posted the answer. Should I use `"took"` field as the answer? I was thinking that it should be `doc_count`. – Dinosaurius Sep 01 '17 at 10:58
No `took` is the number of milliseconds that the response took. – Val Sep 01 '17 at 11:00
URL? It's localhost:9200. Or what do you mean? – Dinosaurius Sep 01 '17 at 11:12
The full URL you're using with the index, etc – Val Sep 01 '17 at 11:13
I run queries from DevTools->Console in Kibana. Not sure if you mean this URL: `http://localhost:5601/app/kibana#/dev_tools/console?_g=(refreshInterval:(display:Off,pause:!f,value:0),time:(from:now-1y,mode:quick,to:now))` – Dinosaurius Sep 01 '17 at 11:19
Can you try with `-2H`... just wondering if this is a DST and/or timezone issue. – Val Sep 01 '17 at 11:22
I tried `-2H` and other values. The same issue all the time. Does it work fine for you with the data that I posted? – Dinosaurius Sep 01 '17 at 11:28
Yes, I get `doc_count: 2` – Val Sep 01 '17 at 11:32
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/153466/discussion-between-dinosaurius-and-val). – Dinosaurius Sep 01 '17 at 11:35

Incorrect output result of "aggs" query

1 Answers1