# Wrong year selected Elastic search SQL Query (also any better way to do this)

**URL:** https://discuss.elastic.co/t/wrong-year-selected-elastic-search-sql-query-also-any-better-way-to-do-this/201972
**Category:** Elasticsearch
**Created:** [October 2, 2019, 2:26pm UTC](https://discuss.elastic.co/t/wrong-year-selected-elastic-search-sql-query-also-any-better-way-to-do-this/201972 "2019-10-02T14:26:34Z")
**Posts on this page:** 4
**Page:** 1

<div class="post-metadata">

### Author: ![Avinash\_D\_Silva](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/avinash_d_silva/32/45312_2.png) [@Avinash\_D\_Silva](https://discuss.elastic.co/u/Avinash_D_Silva)
#### Post date: [October 2, 2019, 2:26pm UTC](https://discuss.elastic.co/t/wrong-year-selected-elastic-search-sql-query-also-any-better-way-to-do-this/201972/1 "2019-10-02T14:26:34Z")

</div>

```auto
POST _sql
{
  "query": "SELECT AVG(parameters.strength.numericValue) as signal_strength, DAY_OF_MONTH(timestamp) as dom, MONTH_OF_YEAR(timestamp) as moy, YEAR(timestamp) as yr FROM \"performance*\" where name='Monitor' and request.requester='lane' and timestamp > TODAY() - INTERVAL 30 DAYS group by dom,moy,yr"
}

```

I get the result as

```auto
#! Deprecation: [interval] on [date_histogram] is deprecated, use [fixed_interval] or [calendar_interval] in the future.
{
  "columns" : [
    {
      "name" : "signal_strength",
      "type" : "double"
    },
    {
      "name" : "dom",
      "type" : "integer"
    },
    {
      "name" : "moy",
      "type" : "integer"
    },
    {
      "name" : "yr",
      "type" : "integer"
    }
  ],
  "rows" : [
    [
      4.300000190734863,
      1,
      10,
      2018
    ],
    [
      4.3742858341762,
      2,
      10,
      2018
    ],
    [
      4.37368433099044,
      24,
      9,
      2018
    ],
    [
      4.342666816711426,
      25,
      9,
      2018
    ],
    [
      4.390000104904175,
      26,
      9,
      2018
    ]
  ],
  "cursor" : "xxxxx=="
}

```

The year seems to be wrong, it should be 2019 and not 2018, I confirmed that `timestamp` definitely is 2019.

Also would like to know if there is any better way to do this?

Also the actual doc looks something like this:

```auto
{
  "_index": "performance_2019-10-02",
  "_type": "_doc",
  "_id": "wdGkjG0BWGRtqQ3nsOXS",
  "_version": 1,
  "_score": null,
  "_source": {
    "timestamp": "2019-10-02T09:24:26",
    "request": {
      "tags": {
        "id": "29722"
      },
    ......
    },
    "name": "Monitor",
    "parameters": {
      .....
    }
  },
  "fields": {
    "timestamp": [
      "2019-10-02T09:24:26.000Z"
    ]
  },
  "sort": [
    1570008266000
  ]
}

```

---

<div class="post-metadata">

### Author: ![Avinash\_D\_Silva](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/avinash_d_silva/32/45312_2.png) [@Avinash\_D\_Silva](https://discuss.elastic.co/u/Avinash_D_Silva)
#### Post date: [October 2, 2019, 3:52pm UTC](https://discuss.elastic.co/t/wrong-year-selected-elastic-search-sql-query-also-any-better-way-to-do-this/201972/2 "2019-10-02T15:52:28Z")

</div>

ok seems like it is an existing BUG is ElastisSearch:

> <https://github.com/elastic/elasticsearch/issues/47450>

---

<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 30, 2019, 3:52pm UTC](https://discuss.elastic.co/t/wrong-year-selected-elastic-search-sql-query-also-any-better-way-to-do-this/201972/3 "2019-10-30T15:52:29Z")

</div>

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

---

<div class="post-metadata">

### Author: ![matriv](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/matriv/32/43656_2.png) [@matriv](https://discuss.elastic.co/u/matriv)
#### Post date: [October 30, 2019, 10:54pm UTC](https://discuss.elastic.co/t/wrong-year-selected-elastic-search-sql-query-also-any-better-way-to-do-this/201972/4 "2019-10-30T22:54:30Z")

</div>

FYI issue has been fixed and will be available with 7.5.0