Show cumulativeSum's for 24h periods that reset to 0 at midnight

InfluxDB 2
1

I collect kW data with peak measurements around every 1-5 seconds.
My current solution is, to “mean” aggregate hourly (in order to come up with a meaningful representation of kWh (for the hour).

I now aim to representing accumulated “positive” kWh values from that aggregation,
but the cumulated sum needs to reset to 0 at midnight.

here is what I have so far:

from(bucket: “default”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r[“_measurement”] == “power”)
|> filter(fn: (r) => r[“tag”] == “isv”)
|> filter(fn: (r) => r[“_field”] == “p1” or r[“_field”] == “p2” or r[“_field”] == “p3” or r[“_field”] == “p4” or r[“_field”] == “p6” or r[“_field”] == “p5”)
|> drop(columns: [“tag”, “topic”, “host”, “_measurement”, “result”])
|> map(fn: (r) => ({r with _value: r._value * -1.0}))
|> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
// group by time to isolate the values you want to sum together
|> group(columns: [“_time”], mode:“by”)
|> sum(column: “_value”)
// to ungroup your data, provide a group without any columns
|> group()
|> yield(name: “grid difference”)

2

Welcome @tutenchamun

Since your values are always rising and reset every day at 00:00:01, can you try this? You may need to add some extra lines (just looking at your currently proposed query)…

from(bucket: "default")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r["_measurement"] == "power")
|> filter(fn: (r) => r["tag"] == "isv")
|> filter(fn: (r) => r["_field"] == "p1" or r["_field"] == "p2" or r["_field"] == "p3" or r["_field"] == "p4" or r["_field"] == "p6" or r["_field"] == "p5")
|> aggregateWindow(every: 1d, fn: spread, createEmpty: false) // calculates the amount per day using the spread
|> sum() // sums the above 24-hour periods
|> yield(name: "grid difference")
3

thank you and sorry for not answering … I had been quit busy

… I had not been able to achieve with your proposed solution.
I need to say some more about the data I am working with:

  • my power meter continuously produces watt measurements (negativ/production, positive/consumption) (MQTT, Telegraf, influx2)
  • every other second there is an MQTT message with the measurements of 6 measure-points (p1-p6) with exactly the same timestamp

here is my initial query:

from(bucket: “default”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r[“_measurement”] == “power”)
|> filter(fn: (r) => r[“tag”] == “isv”)
|> filter(fn: (r) => r[“_field”] == “p1” or r[“_field”] == “p2” or r[“_field”] == “p3” or r[“_field”] == “p4” or r[“_field”] == “p6” or r[“_field”] == “p5”)
|> drop(columns: [“tag”, “topic”, “host”, “_measurement”, “result”])

Step 1 (graph 1)
I invert all datapoints (that is just a representational decision)

|> map(fn: (r) => ({r with _value: r._value * -1.0}))

Step 2 (graph 1)
each of the 6 data series (p1-p6 / watt (consumption) measurements) are continuously sent to influxdb2 every other second with the same timestamp.
In order to achieve a net grid power I sum up every (inverted) field (p1-p6) grouped and summed up with their common timestamp

|> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
// group by time to isolate the values you want to sum together
|> group(columns: [“_time”], mode:“by”)
|> sum(column: “_value”)
// to ungroup your data, provide a group without any columns
|> group()
|> yield(name: “grid difference”)

Step 3: (unsolved - graph 2)
Now I need to aggregate the mean sum in 1h packages, in order to convert the measures to a meaningful kWh representation.
Maybe this can be solved within the line below already ?

|> aggregateWindow(every: 1h, fn: mean, createEmpty: false)

Step 4: (unsolved - graph 3)
Finally I want to generate a curve that cumulates the hourly kWh packages
that resets to 0 at midnight

… maybe it is needed to solve the problem with different graphs ??!