Get average watts used from smart plug for "spikey" device

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:

  1. It will give a divide by zero error if there is only one reading.
  2. 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”!