Fine-tuning plots of of daily and hourly averages

Grafana Time Series Panel
1

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:

image
image2362×598 58.2 KB

image
image2358×596 59.8 KB

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.

Cheers,
Torsten

2

Hello,
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?

image
image887×241 40.8 KB

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

3

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.

image
image2334×570 58.5 KB

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:

image
image2362×598 59.7 KB

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.

4

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.

5

Hello, i have a query like this:
SELECT
$__timeGroup(A, ‘1h’) AS time,
SUM(E) AS value
FROM
tblPhuc
WHERE
$__timeFilter(A) AND A >= ‘2024-04-17 00:00:00’ AND A <= ‘2024-04-17 23:59:59’
GROUP BY
$__timeGroup(A, ‘1h’)
ORDER BY
$__timeGroup(A, ‘1h’);
But I cannot execute the where condition. No matter how I adjust the where, it will still take the time until 11pm. please help me fix, thanks

6

Click on the query inspector and click on first tab of screen that comes. what does the actualy query look like?