Average per hour

1

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.

3

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?

5

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)

6

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.

7

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)

8

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.

9

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.

10

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.

13

You can achieve that with the count function.
Just use the following query:
SELECT count(“longest-wait-time”)/3600 FROM “web” WHERE (“service” =~ /web1|app1/) AND $timeFilter GROUP BY time(1h), “service” fill(null)

What happens, is that you are going to aggregate the data by 1h chunks and after you will do an average of those values.

The division value, in this case /3600 it will depend on the resolution of your database, if it is sec by sec you want the average value of 3600 data points in 1h, if it’s min by min you want the average value of 60 data points in 1h, etc…

It’s late, but hope this helps :smiley:

14

Hi, I managed to get exactly what you need using flux:

import "experimental"
import "date"

baseTime = v.timeRangeStart
timeStart = date.truncate(t: experimental.subDuration(d: 7d, from: now()), unit: 1d)
timeStop = date.truncate(t: experimental.addDuration(d: 1d, to: now()), unit: 1d)

energyUsage = from(bucket:"smartmeter/autogen") 
|> range(start: timeStart, stop: timeStop) 
|> filter(fn: (r) => r._field == "EnergyImportTotal") 
|> aggregateWindow( every: 1h, fn: mean) 
|> window(every: 1d)
|> stateDuration(
      fn: (r) => true,
      column: "timeDiff",
      unit: 1ns
    )
|> map(fn: (r) =>
      ({ r with _time: time(v: (int(v: baseTime) + r.timeDiff)) })
    )
|> drop(columns: ["timeDiff"])
|> group(columns: ["_time", "Serial"])
|> mean()
|> group(columns: ["Serial"])
|> yield()

What this does is basically calculating the hourly averages, then moves the times of all measurements to the same day and calculates the averages again. Depending on your data structure the query has to be adapted.

image
image970Ă—295 13 KB

2 Likes
15

This would be a great feature - sounds like the USP of the way the data is stored causes this limitation in display requirement.
In principle what you @MarioG is doing is a smart way to achieve!
I only comment with a minor observation (which may be wrong as I couldn’t follow exactly what you were doing), but it looks like you may be averaging averages. If this is the case that may skew the real answer - although this slight skew would probably be acceptable for the end result.

16

Hello @teambates welcome and thank you @MarioG for your help!
Can you please create a feature request? GitHub - influxdata/flux: Flux is a lightweight scripting language for querying databases (like InfluxDB) and working with data. It's part of InfluxDB 1.7 and 2.0, but can be run independently of those.