# Elasticsearch returns wrong result on Date range

**URL:** https://discuss.elastic.co/t/elasticsearch-returns-wrong-result-on-date-range/98977
**Category:** Elasticsearch
**Created:** [August 31, 2017, 9:29am UTC](https://discuss.elastic.co/t/elasticsearch-returns-wrong-result-on-date-range/98977 "2017-08-31T09:29:50Z")
**Posts on this page:** 2
**Page:** 1

<div class="post-metadata">

### Author: ![Saurab\_Dahal](https://avatars.discourse-cdn.com/v4/letter/s/50afbb/32.png) [@Saurab\_Dahal](https://discuss.elastic.co/u/Saurab_Dahal)
#### Post date: [August 31, 2017, 9:29am UTC](https://discuss.elastic.co/t/elasticsearch-returns-wrong-result-on-date-range/98977/1 "2017-08-31T09:29:50Z")

</div>

My purpose is to get all result between `bill\_date \>= 2017-07-30 and bill\_date \<= 2017-08-30". But it gives me weired result.  
My query is simple

```
{
  "size": 0,
  "query": {
    "bool": {
      "must": [
        {
          "range": {
            "bill_date": {
              "gte": "2017-07-31",
              "lte": "2017-08-30"
            }
          }
        },
        {
          "term": {
            "division": "FMCG FOOD"
          }
        },
        {
          "term": {
            "store_code": "1"
          }
        },
        {
          "term": {
            "icode": {
              "value": "BM7185"
            }
          }
        }
      ]
    }
  },
  "aggs": {
    "product_code": {
      "terms": {
        "field": "icode",
        "size": 3000
      },
      "aggs": {
        "salesAmt_stats": {
          "stats": {
            "field": "totalAmt"
          }
        },
        "qty_stats": {
          "stats": {
            "field": "qty"
          }
        }
      }
    }
  }
}

```

This query has no syntax error, and the result I get is

```
{
          "key": "BM7185",
          "doc_count": 2,
          "qty_stats": {
            "count": 2,
            "min": 1,
            "max": 1,
            "avg": 1,
            "sum": 2
          },
          "salesAmt_stats": {
            "count": 2,
            "min": 76,
            "max": 80,
            "avg": 78,
            "sum": 156
          }
        }

```

Which is clearly the wrong result, but if I change the range for bill\_date to `gte:2017-07-01 and lte:2017-07-30" I get

```
{
          "key": "BM7185",
          "doc_count": 141,
          "qty_stats": {
            "count": 141,
            "min": 1,
            "max": 4,
            "avg": 1.049645390070922,
            "sum": 148
          },
          "salesAmt_stats": {
            "count": 141,
            "min": 75,
            "max": 320,
            "avg": 82.68794326241135,
            "sum": 11659
          }
        }
      ]
    }

```

Here the sum is 148 for `"2017-07-01 to 2017-07-30"` which is true, but why is it showing sum = 2 for `2017-07-31-2017-08-30`  
My schema is

```
"mappings": {
      "dotnet": {
        "properties": {
          "bill_date": {
            "type": "date",
            "format": "yyyy-mm-dd"
          },
          "division": {
            "type": "keyword"
          },
          "icode": {
            "type": "keyword"
          },
          "mrp": {
            "type": "double"
          },
          "qty": {
            "type": "double"
          },
          "store_code": {
            "type": "keyword"
          },
          "totalAmt": {
            "type": "double"
          }
        }
```

---

<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: [September 28, 2017, 9:39am UTC](https://discuss.elastic.co/t/elasticsearch-returns-wrong-result-on-date-range/98977/2 "2017-09-28T09:39:06Z")

</div>

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