Aggregating monthly deltas from a timeseries table

[arminus]

I have a very simple influxdb table with timestamped values for power consumption. The values cumulate, for the daily chart I use this query:

SELECT difference(last("value")) FROM "infinite"."sonoff.0.DVES_4C6100.ENERGY_Total" WHERE $timeFilter GROUP BY time(1d)

Now I’d like to build another chart showing the monthly values, but all I’ve come up with is this

SELECT difference(last("value")) FROM "infinite"."sonoff.0.DVES_4C6100.ENERGY_Total" WHERE $timeFilter GROUP BY time(30d)

But that is just an aggregation of 30 days groupings relative to the current date and not the aggregation for Jan, Feb, Mar and so forth.

I could of course write a couple of lines of python code to build a kind of month table, but I wonder if there’s is an easier solution to this - given that this looks kind of like a standard pattern?

[grant2]

Hi @arminus

I believe that InfluxQL lacks aggregation or windowing by calendar month. This was introduced in Flux in 2019.

Have you considered upgrading to Flux or SQL (Influx 3.0)?

In Flux, I believe it would be:

  |> aggregateWindow(every: 1mo, fn: last)
  |> difference()

and in SQL, I believe it would be:

SELECT date_Bin(INTERVAL '1 month', time, TIMESTAMP '1970-01-01 00:00:00Z') AS time, difference(last("value")) AS ....

[arminus]

Thanks for the hint, with this flux query

from(bucket: "default")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "sonoff.0.DVES_4C6100.ENERGY_Total")
  |> filter(fn: (r) => r["_field"] == "value")
  |> aggregateWindow(every: 1mo, fn: last)
  |> difference()

I get this table:

The problem here is that the timestamps are all “shifted plus one day”, i.e. if I visualize this as is in a bar chart (with x-axis time unit set to time: MM), I get the values for 06 displayed for 07 and so on, and 11 appears twice.

Or is this an issue with the UTC offsets in the timestamps - which appears on all but the last row? This is how the flux query result looks like in the influx explorer:

[grant2]

Hi @arminus

In my test data, I had a similar issue with my data and this change to the aggregateWindow statement seemed to fix it:

  |> aggregateWindow(every: 1mo, offset: 1d, fn: last, timeSrc:"_start")

[arminus]

Perfect, thanks a lot!

[arminus]

I’m afraid, there’s one more problem with this. The offset 1d fixes the issue for previous months, but causes problems for the current month.

Here’s the raw data for start of Oct until today with the manually calculated deltas:

2023-10-01 00:00:00+0200;8375.5342
…
2023-11-01 00:00:00+0100;8731.1057 → 355.5715
…
2023-11-21 08:52:00+0100;9022.5506 → 291.4449

and this is what aggregateWindow calculates for these 2 months:

2023-10-02 02:00:00 → 355
2023-11-02 01:00:00 → 279

279 is kind of close to the actual delta between
2023-11-01 00:00:00+0100;8731.1057
and
2023-11-20 00:00:00+0100;9007.4616

but not quite, so I’m really confused about what’s going on here.

[arminus]

Well, I did what I always do in such situations: Wrote some python code to preprocess the raw data into a 2nd db which I use as input for the Grafana chart.