Monthly bar graph shifted

Grafana Dashboards
1
  • What Grafana version and what operating system are you using?

Grafana v9.2.3
InfluxDB 1.8.10
Debian 11

  • What are you trying to achieve?

I want to create a monthly bar chart for electricity generated by a PV system.

  • How are you trying to achieve it?

I create a bar chart from the energy produced counter with the following settings:
Query options - Relative time: “12M/M”
select: field(value) spread()
group by: time(30d)

  • What happened?

I get nealy what I expected, but the months changes not at the first day of a month but on the 18th or 19th day. So what you see in the attached picture is 2024-01 is the energy produced from 2024-01-18 to now. And 2023-12 is the energy produced from 2023-12-18 to 2024-01-17.

  • What did you expect to happen?

I would expect starting in 2024-01-01 and then a bar for every 30d back.

  • Can you copy/paste the configuration(s) that you are having problems with?

SELECT spread(“value”) FROM “Solar_1OGli_ac_total” WHERE $timeFilter GROUP BY time(30d) fill(null)

  • Did you receive any errors in the Grafana UI or in related logs? If so, please tell us exactly what they were.

no

  • Did you follow any online instructions? If so, what is the URL?

no

2024-01-24 09_01_19-Window
2024-01-24 09_01_19-Window1898×883 98.8 KB

2

Hi @will7362

I am not sure I understand the problem. You want to have a bar graph where each bar represents the 00:00:00 on 18th of one month to 23:59:59 on the 17th of the next month, correct?

It would seem your query and graph that you shared do the aggregation by 30 days (not the same as a monthly). As far as I know, InfluxQL supports only basic aggregations (day, week) at this time, but Flux and SQL do support more customizable aggregations. More here.

3

Thanks for your reply. No, I want in best case each bar starting on the 1st of a month. But with the configuration shown a new bar starts on the 18th of a month ± a day or two. I thought with the relative time “12M/M” it should start with the 1st of a mounth, but I can´t explain this 18 days.

To make it a little bit more clear: On the January 18th for example the last bar appeared with the energy from this time to now (starting at zero and growing every day). On December 19th the penultimate bar appeared showing the energie produced von Dezember 19th to January 18th. But I want Dezember 1st to January 1st and so on.

4

I think I can now explain where these 18 days come from. Apparently, 30 days are counted from January 1, 1970. In the pictures you can see the table view of Grafana
2024-01-30 10_43_25-Explore - openhab_home - Grafana – Mozilla Firefox

and 30 days added up in a spreadsheet from January 1st, 1970.
2024-01-30 10_43_44-Unbenannt 1 - LibreOffice Calc

Can you change the behavior so that it counts backwards from the 1st of the current month or, for example, forwards from July 1st, 2023 instead of January 1st, 1970?

5

I found a solution for myself:

I changed GROUP BY 30d to GROUP BY 2629746s

2629746 seconds is the average length of a month with all leap year rules over 400 years.

This way the cut is always on the 31st to the 3rd of a month. That’s enough for me.

1 Like
6

I don’t understand… this issue “Group by month” is so common… here it is 1s, 1m, 1h, 1d. is it that hard to add this f*** “1M”??? or +“1Y”? I’ve already spent 4 days trying to make a monthly bar, I’ve read hundreds of posts and still can’t find direct instructions on how to make the grafana count exactly every month from the 1st day 00:00 to the last day 00:00