InfluxDB: Grouping data by month in Grafana

Hi,

Has anyone solved or figured out a way to group data (influxdb >1.8) by month?
I’m in a process of migrating all persistence from jdbc/mariadb to influxdb but my biggest problem is grouping kWh data monthly (the max value monthly as I reset all kWh counters every month) and representing it in grafana.
Grouping is simple in (my)SQL but I can’t seem to figure this out in Influxdb… I read some about it in this issue but I don’t get the syntax for this nor how to get it working in grafana.

Anyone else figured this out already? :thinking:

Bumping this thread a little as I still haven’t figured this one out :wink:

Hi,
can you not just use interval(30d)?

At least that’s working for me…

Could be? could you give a complete example of the query :slight_smile:

I’m trying to achieve a column graph that displays every item’s “max” kWh value last month (or monthly) by filtering on the item name.
Example (if all kwh items start the name with KWH_):

from(bucket: "openhab")
  |> range($range)
  |> limit(n:1000)
  |> filter(fn: (r) => r._measurement =~ /^KWH_/)

Hi,

I’m sorry I don’t know how it works in FLUX, I only know about the influxQL where I would use: last() difference() with interval(30d) on accumulating values. This will give you a chart of the difference values per 30d periods (e.g. kWh usage per month) without having to reset your meter.

As far as I know, this is not possible with Influxdb 1.x.
But it should be possible with 2.0 and with Flux…

See this : Will InfluxDB 2 support "group by month"? - InfluxDB 2 - InfluxData Community

I did not upgrade to influxdb 2 so can’t confirm…

Hi,

You have to set the query options to 31 days and the query
select: field (value) and spread ()
group by: time(1d) and fill(null)

This works for me,using grafana 7.3.6 and influx 1.8.3
Screenshot 2021-01-11 at 07.44.50|690x231

Hm but that only gives daily values for a single item. I’m trying to get monthly data (max value) from multiple items to compare, ie. how much kWh has been used by each item in Jan and Feb (in a bar gauge/column) :thinking:

i’ve a similar situation, i’m logging my airco usage on a daily basis to compare, only in a graph style and i stack the results.
But you can also monitor this on a monthly basis. I think you have to set the query options to 1y and the different queries to 31d.