Fine-tuning plots of of daily and hourly averages

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:

  1. 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.

  2. 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.


I don’t understand the first point

While for the current hour (or day) the right bar increases, the bar on the left side becomes smaller

what is the first bar ? Y axis in blue ? first point of dataset in red?

For the second point , you can truncate your date string value with some substring() equivalent or ajust time type in standard panel option ( and set date format).

Many thanks for the suggestion regarding the date string - I will look into it. Regarding the first point it’s the first point you marked in red - and the last on the right side of the plot. It’s easier to see in the 24h summary.

The following snapshot has been taken at 16:23 with 35.6Wh on the left and 19.6Wh from the current hour.

As the data for this hour is coming in the 19.6Wh increase, whereas the 35.6Wh decrease. See this snapshot taken a few minutes later:

EDIT: I could not find a way to manipulate the date format via standard options but changing

interval_hour = MM/DD

in grafana.ini made the 02:00 disappear.

i think it’s normal because you have a shifting time windows of 24 hours,
and your first point became out of the range. i think you can fix the start time with absolute value in the query like “24h” this way it’s should remove your point hour by hour if you see what i mean.