Group by every hour of the day

Fluxlang
1

Hello, Im new to Flux and i dont get it how to group my Data to get an “mean” by every hour of the day.
I have measurements of power consumtion every minute. I want to see on what hour of the day i use how many power. So the result should be 24 mean values without a date.

group(columns: ["_time"])

This group function doesn’t seem to be capable to group only by the hour.

2

Hi, welcome.

In flux there’s a specific function to do that it’s window()

You’d then pipe into mean or median to get the stat for that window.

Then, depending on your use case, you might represent the last 10rows in a table/grid. Or if you wanted to collapse the window groups back into a single series, you’d pipe again into window(set to infinity) . Pretty much all the explanation is on the window data doco page.

Eg.

 dataSet
    |> window(every: 1m)
    |> mean()
    |> duplicate(column: "_stop", as: "_time")
    |> window(every: inf)

Feel free to chime in here again if you have further questions

3

Hi, thanks for your explanation. But this will give me intra-day hourly groups for each day.

 dataSet
    |> window(every: 1h)

What i want ist 24 Groups across the whole dataset. One per hour of a day (0,1,2 … 22,23 o’clock).

4

Hi,

Can you try using date.hour() to add another column to every row of your dataset and then group by that column to get mean() for every hour?

Example:

import "date"
from(bucket: "mybucket")
	|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "measurement")
  |> filter(fn: (r) => r["_field"] == "field")
  |> map(fn: (r) => ({ r with hour: date.hour(t: r._time) }))  
  |> group(columns: ["hour"], mode:"by")
  |> mean(column: "_value")

Hopefully this is what you are looking for. Let us know if this does not work.

Regards
Balaji

1 Like
5

yeha, that is the result i’m looking for.

After a bit of trail and error this is my query:

import "date"
from(bucket: "iobroker")
  |> range(start: v.timeRangeStart, stop:v.timeRangeStop)
  |> filter(fn: (r) =>
    r._measurement == "smartmeter.0.1-0:1_8_0__255.value"  and
    r._field == "value"
  )
  |> map(fn: (r) => ({ r with hour: date.hour(t: r._time) }))  
  |> difference()
  |> group(columns: ["hour"], mode:"by")
  |> mean(column: "_value") 
  |> group()
  |> sort(columns: ["hour"])

To use it in Grafana i needed to transform the “hour” to string.

Thank all of you .