I am running Grafana 9.1.5 and influxDB 1.8.10 on raspberry PI. Every minute a Shelly Plus 1PM is sending (amongst other variables) the power consumption of the previous minute as “mWh” to influxDB. The timestamp is UTC.
In Grafana I am plotting power, voltage, current, etc for the previous 24h for browser time (CEST). This works perfectly well. I also want to plot bar charts for a) the hourly consumption for the past 24h and b) the daily consumption for the past week.
I added the following two queries
SELECT sum("energy_last_minute") / 1000 FROM "PV" WHERE ("location" = 'P13') AND $timeFilter GROUP BY time(60m) fill(null)
with “Relative Time 24h” and
SELECT sum("energy_last_minute") /1000 FROM "PV" WHERE ("location" = 'P13') AND $timeFilter GROUP BY time(1440m) fill(null)
with “Relative Time 7d”. This turns mW into W and sums up a) 60 values to an hourly consumption
and b) 60x24 = 1440 values to a daily consumption. I hope that everything is fine up to here.
I am getting the two plots:
Now the two concerns:
While for the current hour (or day) the right bar increases, the bar on the left side becomes smaller. I guess that is as expected as the relative 24h (or 7d) time windows travels to the right. Is there a way to keep the hourly (or daily) average constant for the first bar? I have seen suggestions to use subqueries (calculate the average, then plot that variable) but I have no clue how to do that. I am using InfluxQL.
The daily plot’s horizonal axis shows the day/month date plus “2:00”, probably due to the UTC → CEST conversion. Is it possible to get rid of the time and keep the day/month? Or shift the labels by 2h? I know that I can shift data but that does not affect the labels of the axis.