Monthly usage based on daily (cumulative) values

Fluxlang
1

Disclaimer: Douple post because of no answers in the InfluxDB 2 forum.

Hi all,

I have data stored from my energy meter which looks like this:

image

after this query:

currentvalues = from(bucket: "smarthome")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "ElectricalEnergy")
  |> filter(fn: (r) => r["Scope"] == "House")
  |> filter(fn: (r) => r["Source"] == "SaiaBurgess")
  |> filter(fn: (r) => r["UpdateInterval"] == "Day")
  |> filter(fn: (r) => r["_field"] == "value")
  |> truncateTimeColumn(unit: 1m)

So, a value every day at midnight (in reality, they are some seconds after midnight why I truncate them).

How can I now get the monthly usage? I’ve tried so many things with aggregateWindow, timeshift and all that it would take too long to explain it all here. I think one of the “problem” is, is that the consumption of e.g. January hast to be calculated until 00:00 on Feb 1st. As this is already Feb. I don’t know the correct way of taking this into account.

Did anyone already do that?

Many thanks,
Oliver

2

@Sparx82 Just to understand the problem I will use an example here.
I am using generate.from() function to generate some data. Here is the generated data.

Table: keys: [_start, _stop]
                   _start:time                      _stop:time                      _time:time                  _value:int
------------------------------  ------------------------------  ------------------------------  --------------------------
2021-01-01T00:00:00.000000000Z  2021-04-01T00:00:00.000000000Z  2021-01-01T00:00:00.000000000Z                           0
2021-01-01T00:00:00.000000000Z  2021-04-01T00:00:00.000000000Z  2021-01-16T00:00:00.000000000Z                           2
2021-01-01T00:00:00.000000000Z  2021-04-01T00:00:00.000000000Z  2021-01-31T00:00:00.000000000Z                           4
2021-01-01T00:00:00.000000000Z  2021-04-01T00:00:00.000000000Z  2021-02-15T00:00:00.000000000Z                           6
2021-01-01T00:00:00.000000000Z  2021-04-01T00:00:00.000000000Z  2021-03-02T00:00:00.000000000Z                           8
2021-01-01T00:00:00.000000000Z  2021-04-01T00:00:00.000000000Z  2021-03-17T00:00:00.000000000Z                          10

Then I am using aggregateWindow to generate the monthly sum.

import "generate"

timeRange = {start: 2021-01-01T00:00:00Z, stop: 2021-04-01T00:00:00Z}

data =
    generate.from(count: 6, fn: (n) => n + n, start: timeRange.start, stop: timeRange.stop)
        |> range(start: timeRange.start, stop: timeRange.stop)

data
    |> aggregateWindow(every: 1mo, fn: sum)

Result: _result
Table: keys: [_start, _stop]
                   _start:time                      _stop:time                      _time:time                  _value:int
------------------------------  ------------------------------  ------------------------------  --------------------------
2021-01-01T00:00:00.000000000Z  2021-04-01T00:00:00.000000000Z  2021-02-01T00:00:00.000000000Z                           6
2021-01-01T00:00:00.000000000Z  2021-04-01T00:00:00.000000000Z  2021-03-01T00:00:00.000000000Z                           6
2021-01-01T00:00:00.000000000Z  2021-04-01T00:00:00.000000000Z  2021-04-01T00:00:00.000000000Z                          18

Kindly let me know if this is what you are looking for.

3

Unfortunately aggregateWindows would be obvious but is not working as I need.
First of all, the values are cumulative, meaning that I have to calculcate the difference between them. Also not an issue, but the “problem” is the counter value is always stored at 00:00, meaning the counter value at 00:00 of the first day of the next month contains basically the energy consumption of the last day of the month. And aggregateWindow does not count that because the value is already in the new month. And if I store the value at let’s say 23:59 I just have the same problem that theres no value for the first day of the month…