I have a tplink sensor plug on a boiling water tap, and I wanted to know what the average power use was. The tap idles most of the time at < 1W, then occasionally spikes to around 2KW for just a few seconds. As the sensor plug only takes readings every 30s there’s a very good chance it will miss these times. So, I decided to use the total kwh attribute instead. I wanted to do it over 24h, so the only way I could work out to do this was to use sql (my first time for HA).
This is what I ended up with, and it seems to work a treat. I’m happy to hear any suggestions for improvements though. It will cope with having less than 24h of stats (will take maximum up to that).
I got my inspiration / sql help from this but I think this is sufficiently different that it is useful to share.
sensor:
- platform: sql
queries:
- name: 'plug_boiling_water_tap_watts_24h'
query: >
select
round( (kwh_new - kwh_old) * 1000 / ( (JulianDay(date_new) - JulianDay(date_old)) * 24 ),2 ) 'watts'
from (
select state as kwh_old, min(created) as date_old from "states"
where entity_id = 'sensor.plug_boiling_water_tap_kwh_total'
and state != 'unknown' and state != ''
and created > datetime('now', '-24 hours')
),(
select state as kwh_new, max(created) as date_new from "states"
where entity_id = 'sensor.plug_boiling_water_tap_kwh_total'
and state != 'unknown' and state != ''
);
column: 'watts'
unit_of_measurement: W
Two potential issues I’m aware of but seem edge/rare enough not to code for:
- It will give a divide by zero error if there is only one reading.
- If the total kwh gets reset to zero, this won’t give useful data until 24h after I guess.
Hope that’s of some use to someone. Very modest but my first “share”!