Graphing energy usage in Grafana

Could you go through this step by step Manu? Want to achieve exactly the same thing as you, but have been unsuccessful untill now…

1 Like

How do you guys do “Monthly” graphing… ie calculate your monthly values. Influxdb doesn’t have “GROUP BY MONTH”

I’m also very interested in this! The “difference” selector helped me get the good values when I want “day to day-bars” but when I try this per week instead it gives me totally wrong values… before I knew how to make grafana queries I used to reset zwave-nodes every new week and just fetch the last value in grafana, that gave me the right numbers, but I also skrew up the counter making grafana less effective… I felt that it was the wrong way to use it.

Now I use difference with a counter that doens get reset, it constantly grows and when I try the query below I get a value like 30kHw but I know that it actually should be like 68kHw… why does it not show the exact numbers?

SELECT difference(mean(“value”)) FROM “kWh” WHERE (“entity_id” = ‘pump_energy_kwh’) AND $timeFilter GROUP BY time(1w) fill(null)

For those wanting a simpler solution: in 0.87 you can use the utility sensor to achieve daily and hourly kWh readings and feed those into influxdb

2 Likes

Yep, and I did a little write-up for those using the DSMR component (or any other way that results in separate sensors for each power tariff) and will add that to the component’s docs as well tomorrow…

oh and I forgot about this thread, but for those interested, I have figured out a basic dashboard in grafana which is discussed in this thread. I have included a link to a blogpost on it and the json to import the dashboard into your own grafana instance.

Hope it helps someone :slight_smile:

1 Like

It sure does! Thanks!

And the docs for the utility meter component have just been updated to reflect DSMR specific setup: https://www.home-assistant.io/components/utility_meter/

Totally forgot this thread, I solved my issues using the “last” instead of “mean” value in the grafana query. Now I have exact values in my graphs for everyday/week or whatever consumption

Also I have singlestat meters that reset for every day using “difference” and “last” also

Mabye it helps someone :slight_smile:

Although the utility-meter seems nice I prefer to have it in a grafana-dashboard

8 Likes

Looks awesome! Could you share your dashboard’s JSON?

Sorry for the late reply, is this what you wanted?

json model.yaml (159.3 KB)

2 Likes

Hi, I’m a new influxdb/grafana user. I setup daily bar graph for energy consumption and i noticed that bars are resetting at utc time an not at local 00:00. How did you manage this issue (if you did it)?

Hi,

This seems to be a common problem. Even called a bug by a few. I managed to solve it way back. I cant remember the exact thread but this one is pretty close, take a look: https://community.grafana.com/t/wrong-value-in-graph-with-bars/1306/36

Hope it helps :slight_smile:

Ok, i solved daily bar charts adding GROUP BY “time(24h,22h)”, but i have no idea how to have a monthly bar chart… I think that a GROUP BY “time(30d)” doesn’t do the job…
Do you use monthly bars? How?

So I use a GROUP BY 1D. if I set it to 30D it looks mostly right, but not every month is 30 days and when I was trying i couldn’t find a good way to do months.

This is my kWh per day (just change the 1d to 30d). I am coming from an IoTWatt on this counter, but also convert stuff from AEOTEC devices as well as Tasmota flashed devices.

SELECT integral("Watts",1000h) FROM "iotawatt" WHERE ("ct" = 'Main') AND $timeFilter GROUP BY time(1d) fill(null) tz('America/Los_Angeles')

Influx 1.x doesnt support grouping by month, I also use “30d” for the time being. However, influx 2.0 should have support for it. I havent dared to upgrade yet but its on my todo-list.

Grafana is great, the update to latest version gives you some new stuff also

Completely off topic but has anyone managed to upgrade their inflyx 1.x to 2.x? Doesnt seem to be so straigjt forward.

Or as a workaround. Is it possible to gran The data from The new enerfy dashboard to influx? That Would soöve The monthly bar problem Instead of using 30 days

Have been trying to do so, but if I follow: Upgrade from InfluxDB 1.x to 2.2 with Docker | InfluxDB OSS 2.2 Documentation then in the end I have no measurements in DB Home_assistant. There are however values in home_assistant autogen database, but I have some doubts whether that is my complete history…

Hi Mattie, do you maybe have the template or example how you created the energy monitor with Influx2 db? Another really nice dashboard would be Learn how to monitor your energy use at home with a Raspberry Pi, Grafana and Prometheus | Grafana Labs however I don’t think I have all the appropriate hardware. Would rather prefer InfluxDB and export specific entities.

Thanks in advance.

To compare the energy usage for multiple month over multiple years, I use this query in Grafana. I use MariaDB in Homeasssistant

SELECT substring(from_unixtime(statistics.created_ts),6, 2) AS month,
MAX(CASE WHEN (substring(from_unixtime(statistics.created_ts),1, 4)) = '2022' AND substring(from_unixtime(statistics.created_ts),12, 2) > 0 AND statistics.state >0 THEN statistics.state END) AS '2022',
MAX(CASE WHEN (substring(from_unixtime(statistics.created_ts),1, 4)) = '2023' AND substring(from_unixtime(statistics.created_ts),12, 2) > 0 THEN statistics.state END) AS '2023',
MAX(CASE WHEN (substring(from_unixtime(statistics.created_ts),1, 4)) = '2024' AND substring(from_unixtime(statistics.created_ts),12, 2) > 0 THEN statistics.state END) AS '2024'

from homeassistant.statistics
WHERE statistics.metadata_id = 112
GROUP BY month ORDER BY month;

1 Like