SQL SELECT Command for Days and Hours Avererage

[pgervais (Peter Gervais)]

I have documents in an index that is timestamped. There is an index entry for each hour of the day, for each day of the month with multiple months in the index. Assume each hour document contains CPU MIPS values so the average would be AVG(MIPS).

I want to generate a SQL select statement that:

1. calculates the AVG(MIPS) for each 24 hour in a day resulting in one value for each.
2. for each day over a given start and end date.

Therefore there would be one return average value for each day over the selected date range i.e. 30 days for a given month. Therefore 30 return values , one for each day where each day represents the AVG over its corresponding 24 hour period.

What would the Elasticsearch SQL select statement be to generate this expected result?

[Andrei_Stefan]

SELECT HISTOGRAM(timestamp, INTERVAL 1 DAY) AS day, AVG(mips) FROM test WHERE timestamp BETWEEN '2019-01-01' AND '2019-09-01' GROUP BY day