How to query the maximum value per day, but only where the hour is 23

Grafana Dashboards
1

I have InfluxDB being populated by Home Assistant and am trying to produce a ‘maximum’ value per day chart. The metric being graphed is the ongoing savings made in fuel, so is constantly rising and reverts to zero at midnght.

What I have is working except the data isn’t brilliant as there are often values just after midnight that are clearly from the previous day, before whatever process drops the value back down to zero.

My query is currently:
SELECT max("value") FROM "GBP" WHERE ("entity_id"::tag = 'electricity_savings') AND $timeFilter GROUP BY time(1d) fill(null)

or:
image

I need a way to ignore those early morning anomalies, perhaps by returning the maximum value only when the hour is 23, for example?

I’m a little stumped on how to achieve this?

Thanks

2

Actually I think I’ve done it - by replacing the max() with last() which returns only the last value that day.

3

which might not be the max value

hour,value
1,999
…
…
24,-1 <— last()

4

By nature of the data it will only grow during the day, the challenge is that the reset to zero may not quite happen for the first data point per day, so max() may pick this one. The last datapoint of the day should always be the highest for that day.

1 Like