Average per hour

Hi all,

Trying to get the average per hour for the last 7 days. So for the last 7 days at 1am, the average number of requests is 50. At 8am it is 1000 etc.

X axis would be 00:00 - 23:59, in hourly increments. Y axis would be # of requests.

Is this possible? If so, any tips or pointers to get me going down the right path would be appreciated.

Hi - forgot to mention the data is stored in Influxdb. Here is the query:

SELECT distinct(“longest-wait-time”) FROM “web” WHERE (“service” =~ /web1|app1/) AND $timeFilter GROUP BY time($__interval), “service” fill(null)

Do I need to construct a CQ to get the hourly average?

That looks as Grafana query…
So I think this is it:

SELECT MEAN("longest-wait-time”) FROM “web” WHERE (“service” =~ /web1|app1/) AND $timeFilter GROUP BY time(1h), “service” fill(null)

Thanks.I don’t think this gets me what I am looking for though. The X axis should never change, and will always be a 0-24 hours.The query should allow a user to pick a different timeframe, and then the data will be averaged from that.

This might be better served in a table. For example, I want to see the average per hour for the past 7 days.
00:00 - 10
01:00 - 23
02:00 - 30
…etc
…etc
24:00 - 12

Then if someone want to see same thing but for last 3 days, should be able to change that flag.

Hmm, not sure if I don’t fully understand you or you don’t understand me :smiley:

MEAN("longest-wait-time") …and… GROUP BY time(1h) …returns mean (aka average) of longest-wait-time values during each 1 hour interval.
Then $timeFilter is a Grafana variable that stores the time interval (range)…so in you case Last x days or whatever range you select in Grafana.
So for example plain influxdb query would be:
SELECT MEAN("longest-wait-time”) FROM “web” WHERE (“service” =~ /web1|app1/) AND time > now() - 3d GROUP BY time(1h), “service” fill(null)

Think of it this way. The hours of the day would be static (0-24). The data behind the scenes is what will change depending on the $timeFilter.

Maybe this just isn’t possible.

I think I know what you mean.
So for example you’d like to get an average of all data in following ranges:
07/28 00:00:00 ~ 01:00:00
07/29 00:00:00 ~ 01:00:00
07/30 00:00:00 ~ 01:00:00

And result would be one number displayed in graph at time of 00:00:00.
If so, then yes, it is NOT possible.

Hi, did you find a solution to this? I am collecting energy consumption data to influxdb and would like to show the “today’s hourly average” with the average per hour for the last 7 days in a chart.
For the today’s hourly average I already have a continuous query but have no idea how to do it for the last 7 days.