for the main (I have a Efergy Hub) I want to have the instant power (this I have from the component) and the energy consumed in last hour, last day, last week, last month … eventually rolling (rolling day, rolling week, rolling month) and/or from beginning of a period (last hour, from midnight to now, from first of week to now, from first of month to now) and keeping the data stored/visible in some way that it is easy to check.
The same I would like to do for some appliances (I do have Sonoff POW, Neo coolcam zwave, Broadlink SP3, xiaomi zigbee switch) which all give instant power but is very confusing how they calculate the energy, I am sure you know what I mean
xhrStatus:“complete”
request:Object
method:“GET”
url:“api/datasources/proxy/1/query”
params:Object
db:“home_assistant”
q:“SELECT cumulative_sum(mean(“value”)) FROM “W” WHERE (“entity_id” = ‘efergy_802171’) AND time >= now() - 6h GROUP BY time(20s) fill(null);SELECT mean(“value”) FROM “W” WHERE (“entity_id” = ‘neo_coolcam_power_plug_12a_power’) AND time >= now() - 6h GROUP BY time(20s) fill(null);SELECT mean(“value”) FROM “W” WHERE (“entity_id” = ‘neo_coolcam_power_plug_12a_power_2’) AND time >= now() - 6h GROUP BY time(20s) fill(null)”
epoch:“ms”
data:null
precision:“ms”
response:Object
results:Array[3]
0:Object
1:Object
2:Object
Hi @anon35356645,
no I don’t use Hassio, I use the HA Docker container on a Ubuntu host. It works pretty well so far.
My Continuous Queries are done in Influxdb, you can read them above. I then use Grafana to graph the results.
It looks like in your Grafana query you sum the mean values from your Efergy monitor, which I guess must be instant power readings. I don’t think that will work, as you need to calculate energy use (in wh or Kwh) from the instant power readings (in w or Kw).
My flow is somewhat complicated, as I read the power into an Influxdb database, calculate energy with the integral function, graph in Grafana and use the Influxdb sensor in HA to read for example ‘Energy consumed yesterday’.
I can go through it step by step if you’re really interested.
For things like lights that have fixed power, I think it can be done directly in HA, though I haven’t tried, but this looks good: Calculate power usage
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)
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.
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
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)?
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?
