I have an Influx 2.0 (using Flux) database into which I measure and write my total lifetime electricity usage, indefinitely going up as the kWh's rack up.
I want to, during a time period selected by the user (we'll call this P1), to filter out the day period (let's say day is during 08:00 and 21:00, we'll call these periods, P2) and show how much total the P2 periods used during the overall P1 period.
Essentially, I'll have a lot ranges, which I want to cumulatively add up.
Originally I had a total energy used (which included times outside of the P2 periods) where I just take last value during the end of the P1 period, minus the start of the P1 period. This yielded me with the total electricity used during that period, so I began modifying the query.
I tried the following:
data = from(bucket: "default")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r["entity_id"] == "home_total_kwh")
|> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
values = data |> first() |> findColumn(fn: (key) => true, column: "_value")
start_value = values[0]
data
|> map(fn: (r) => ({ r with _value: r._value - start_value}))
But there is an obvious problem with that, in that this just gives me the total over that time, then I tried filtering over it using the following line, but I'm not sure what to do after:
|> filter(fn: (r) => date.hour(t: r["_time"]) * 100 + date.minute(t: r["_time"]) < 2100 and date.hour(t: r["_time"]) * 100 + date.minute(t: r["_time"]) >= 800)
| time | kWh |
|---|---|
| 00:00 | 2 |
| 02:00 | 4 |
| 04:00 | 6 |
| 06:00 | 8 |
| 08:00 | 10 |
| 10:00 | 12 |
| 12:00 | 14 |
| 14:00 | 16 |
| 16:00 | 18 |
| 18:00 | 20 |
| 20:00 | 22 |
| 22:00 | 24 |
| 00:00 | 26 |
| 02:00 | 28 |
| time | kWh |
|---|---|
| 00:00 | 0 |
| 02:00 | 0 |
| 04:00 | 0 |
| 06:00 | 0 |
| 08:00 | 2 |
| 10:00 | 4 |
| 12:00 | 6 |
| 14:00 | 8 |
| 16:00 | 10 |
| 18:00 | 12 |
| 20:00 | 14 |
| 22:00 | 15 |
| 00:00 | 15 |
| 02:00 | 15 |
Total Used During Day: 15kWh
Sorry for long explanation, any help appreciated :)
Figured it out!
import "date"
from(bucket: "default")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r["entity_id"] == "home_total_kwh")
|> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
|> map(fn: (r) => ({_value: if ((date.hour(t: r["_time"]) * 100 + date.minute(t: r["_time"])) < 2100 and (date.hour(t: r["_time"]) * 100 + date.minute(t: r["_time"])) >= 800) then r._value else 0.0}))
|> reduce(
fn: (r, accumulator) => ({
sum: if (r._value != 0.0 and accumulator.prev != 0.0) then accumulator.sum + r._value - accumulator.prev else accumulator.sum,
prev: r._value,
}),
identity: {sum: 0.0, prev: 0.0}
)
|> keep(columns: ["sum"])
I found how to iterate over every row, therefore letting me create a reduce function such as this, which replace all the values outside of P2 periods with 0, and then iterates to cumulatively sum up all values, and make sure that no sudden bumps in the data happen because of the filtering. Hope this helps whoever comes across this!