Using Grafana v11.3.1 on Home Assistant with a Prometheus database
And I’m gathering energy statistics from sensors for which I created several dashboards.
The dashboards are now defined but what I cannot get to properly display is a bar chart that displays measurements per month starting at the 1st day of a month.
The display currently always starts somewhere in the middle of a month and shifts somewhat.
I’ve attached an picture to show what I now see.
Sure, although the Prometheus query is really simpel and only displays the sensor name.
The sensor automatically calculates the totally consumed energy (in kWh).
Don’t mind the identical values for Sept, Oct and Nov - there was an error due to no measurements were available.
I am not very well-versed in Prometheus, but I think you could rewrite your query to return the total value for each calendar month as: sum(increase(sensor.huisbaasje_energy_this_year_value{mode="gauge"}[1mo])) by (month)
where…
increase() calculates the increase in the metric value over the given time range.
[1mo] specifies a time range of 1 month. This will calculate the increase in the energy value over each calendar month.
{mode="gauge"} is added assuming your metric is a gauge metric (a value that can go up or down). If it’s a counter, you can omit this.
sum(...) by (month):
sum() aggregates the increase values.
by (month) groups the results by calendar month, so you get a separate sum for each month.
This query leverages the increase() function to calculate the difference in your energy metric between the beginning and end of each month. Since your metric is always increasing (my assumption!), this effectively gives you the total energy consumption for that month. The sum() function then adds up any potential intermediate increases within the month (if the data is scraped multiple times within the month).
Note that:
Data availability: This query relies on having data points at the beginning and end of each month to accurately calculate the increase. If there are gaps in your data, the results might be inaccurate.
Time zone: Prometheus might use UTC for time calculations. If you need the results to align with a specific time zone, you might need to adjust the query or Grafana’s time zone settings.
Thanks for the extensive explanation and reply.
Unfortunately it does not seem to be the same on Prometheus and querying - but I will continue to search for a solution!
So it might get a little more complicated with PromQL (AFAIK Grant is a great Influx expert and might have move some tweaks from influx to prometheus). Basically what he said is correct - the question if your metric is a counter or a gauge still stands - the approach might be different. Given the plot, we might assume it’s a counter - since it basically grows all the time.
The increase function is needed, but PromQL doesn’t have anything like 1mo - chat gpt advises to use 30d as people use it as a good estimation - it might not be perfect for you though. There’s also no by (month) (prometheus doesn’t support agregations like that, unless you create a month label). I’m also not sure if you’re able to do exactly monthly increases in PromQL.
You can calculate increase on last 30 days with monthly step (min interval you’ve set in query options). That’s the approximation that might be good enough but I’m afraid it won’t be for you.
So if you’re ready to be a bit bold, come with me on the ride but buckle up (it’s not a solution for the faint-hearted).
Add month and year label to your query - we do it so we can divide our resulting series by month. We can do that by utilizing count_values function (main idea from here). The query with month and year labels would look like this: sum(increase(<your metric>[1d])) + ignoring(month, year) group_right count_values without() ("year", year(timestamp(count_values without() ("month", month(timestamp(time()))) * 0)))
Notice that I’m using increase on 1d (this will be important later).
Now that we know how much it was increasing each day (and we have the series divided by months), we can calculate sum over time from 32 days - it’s the most days the month could have, so we’ll take all the data and no more, since our series are divided by months and years: sum_over_time((sum(increase(<your metric>[1d])) + ignoring(month, year) group_right count_values without() ("year", year(timestamp(count_values without() ("month", month(timestamp(time()))) * 0))))[32d:1d]) (notice the parentheses, because I lost some in the process, then placed them in wrong places and the results were scrambled ). Notice we’re using summation over time in 32 days period and 1 day resolution (if you need more explaining in that part, ping me out).
As for legend and step in the query, use {{month}}/{{year}} or any other format you like - it doesn’t really matter. For Min step use 1M.
You’ll see we got more than one points in each resulting series. We’ll use some transformations to create bar chart, namely Reduce (to get one point for each series), Convert field type (to change our legend to time) and Sort by (to sort the months).
For additional tweaks, you can Organize fields by name so you won’t be stuck with Max in legend.
If the answer seems chaotic, it’s because it is I was wondering if I even should post this answer since I’m not so sure about it working (I also started and scraped it like three times ) - I tested in on some of my data and the results were close enough (error about 0.1%).
). Notice we’re using summation over time in 32 days period and 1 day resolution (if you need more explaining in that part, ping me out).
