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

Elastic Stack Elasticsearch
1 
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

#! 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:

{
  "_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
  ]
}
2

ok seems like it is an existing BUG is ElastisSearch:

3

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

4

FYI issue has been fixed and will be available with 7.5.0