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