Group by Month and Year

Grafana
1

Hi, I want to create a dashbaord were (among other things) the energy consumption per month and year is displayed. I read a lot of threads were other have the same problem / they don´t know how to do this correctly.

I´m running InfluxDB v1.8.4 with Grafana v7.5.3.

At the moment I doing the monthly report like this:
SELECT count(distinct("value")) / 1000 FROM "autogen"."Arbeitszimmer_Steckdose_Schreibtisch_EnergyTotal" WHERE $timeFilter GROUP BY time(30d,-2h) fill(none)

But here I´m using not a “real month”, e.g. with just 28 or 31 days.

Any idea? Is it even possbile?

EDIT: Here I read that it should be possible, but the Link to the article i broken, see How to Build Grafana Dashboards with InfluxDB, Flux & InfluxQL and then " * Window by calendar months and years . Grouping by month works whether a month contains 28, 29, 30, or 31 days, and grouping by year works for regular and leap years."

1 Like
2

It’s not possible with InfluxQL, and it will not be possible in the future. I guess you already saw the GitHub issue thread about this.

If you want to do it, you can use Flux. In fact if you look carefully, the broken link you point to is part of a list titled “Other features unique to Flux”. So yeah, if you’re interested in doing it using Flux check out
https://docs.influxdata.com/influxdb/v2.0/reference/flux/stdlib/built-in/transformations/window/#parameters

3

Ok. Sounds good. But to be honest, I don´t know what´s the difference of flux and influx?

It´s just another “query language”, right?

Are there any disadvantages of flux and will my current queries still work?

4

https://docs.influxdata.com/influxdb/cloud/reference/flux/flux-vs-influxql/

As the above link states, Flux uses functional language patterns that overcome many InfluxQL limitations.

1 Like
5

Do you know another database wich allow grouping by month or year?

1 Like
6

Anything SQL-based should support this, e.g. Postgres, Timescale, MySQL, others too I’m sure

To be clear, this isn’t an inherent limitation of Influx - it’s just related to the (legacy) InfluxQL query language. Querying using the newer Flux language overcomes the limitation.

2 Likes
7

OK, thanks you. We should learn the new query language.

8

This topic was automatically closed after 365 days. New replies are no longer allowed.