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
]
}