Show how long a dishwasher (binary sensor) was on - Average value

Configuration Frontend
1

Hello,
after spending (and failing) the whole afternoon, I’m here to ask for your help.

I want to display in dashboard 2 values:

  • How long a device was on
  • The average time over a time window about the how long a device was on

After a bit of experiments, I found the first part to be working
A smart plug with an energy meter is used on a dishwasher.

Here I define a sensor to determine when the device is on (power consumption > 3.0 watts)

binary_sensor:
    - platform: template
      sensors:
        lavastoviglie_operative_state:
          friendly_name: "Stato Accensione Lavastoviglie"
          value_template: "{{ states('sensor.lavastoviglie_current_power') | float > 3.0 }}"
          device_class: power

Then i defined a timer that starts when the binary sensor switch to state “on”
(thanks Time elapsed counter - #2 by pnbruckner and How long switch has been on for? - #2 by 123 for this)

package_lavastoviglie_timer:
  input_datetime:
    lavastoviglie_last_on:
      has_date: true
      has_time: true

  automation:
    - trigger:
        platform: state
        entity_id: binary_sensor.lavastoviglie_operative_state
        to: "on"
      action:
        service: input_datetime.set_datetime
        entity_id: input_datetime.lavastoviglie_last_on
        data_template:
          datetime: "{{ now().strftime('%Y-%m-%d %H:%M:%S') }}"

    - trigger:
        platform: time_pattern
        seconds: "*"
      condition:
        condition: state
        entity_id: binary_sensor.lavastoviglie_operative_state
        state: "on"
      action:
        service: homeassistant.update_entity
        entity_id: sensor.lavastoviglie_on_time

  sensor:
    - platform: template
      sensors:
        lavastoviglie_on_time:
          value_template: >
            {{ 
              (as_timestamp(now()) - state_attr('input_datetime.lavastoviglie_last_on', 'timestamp')) |timestamp_custom('%H:%M:%S', false) 
              if states('binary_sensor.lavastoviglie_operative_state') == 'on'
              else None
            }}

  recorder:
    exclude:
      entities:
        - input_datetime.lavastoviglie_last_on
        - sensor.lavastoviglie_on_time

And this part is working, few seconds after I power the dishwasher, the timer starts and it stops after the dishwasher ends the washing cycle and power off itself.

Now, moving to the second part. While the first part is not actually storing “how long the dishwasher was on”, it is only a timer, I would like to have a way to display the average duration of when the dishwasher is on.

Basically I’m looking about an average time for the washing cycle.
So that when I compare the timer of current cycle with the average on time, I can roughly estimate how long until the cycle is over.

I tried the platform: statistics, I tried the custom average sensor (Average Sensor), I tried the query method, nothing worked.
Can you help me on this?

Thanks

2

Hello,
after a bit more of experimenting, I found out that the problem with the query approach is that the entity ID is now null (which was my problem), and the filter need to be defined with the metadata table.

My approach is to start from the state when the switch goes “off” and looking back on when it was previously “on”, so that I can identity the time window between the “on” and the “off”. After date, I can calculate the window duration and finally make an average value.
Also, to better refine the results, I exclude all the results where the duration is under 30 minutes (a full cycle I know is no less of 45 minutes, so if I temporary switch on the dishwasher for any reason, that interval is not considered).

I created the following query:

select
	-- s_start.state,
	-- DATETIME(ROUND(s_start.last_updated_ts), 'unixepoch') AS start_ts,	
	-- s_stop.state,
	-- DATETIME(ROUND(s_stop.last_updated_ts), 'unixepoch') AS stop_ts
	-- cast(s_stop.last_updated_ts - s_start.last_updated_ts as int) AS duration
	
	cast(avg(s_stop.last_updated_ts - s_start.last_updated_ts) as int) as value

from states as s_stop
	left join states AS s_start ON s_stop.old_state_id = s_start.state_id

where
	-- "entity_id" column no longer has value
	-- https://community.home-assistant.io/t/all-entity-id-null/555488/16
	s_stop.metadata_id = ( SELECT metadata_id FROM states_meta where entity_id = 'binary_sensor.lavastoviglie_operative_state' )
	
	-- Focus on transition where it is now "off" and it was "on"
	and s_stop.state = "off"
	and s_start.state = "on"
	
	-- Consider only durations that are at least 30 minutes (a full program runs minimum for 45 minutes)
	and (s_stop.last_updated_ts - s_start.last_updated_ts) > 1800

which appaers to retrieve a reasonable value (but I need more washing cycles to create an actual average, right now I have only one entry).

From there, I moved to create a sensor with the given query

sensor:
- platform: sql
  queries:
	- name: lavastoviglie_average_on
	  query: >
		select cast(avg(s_stop.last_updated_ts - s_start.last_updated_ts) as int) as value
		from states as s_stop
		  left join states AS s_start ON s_stop.old_state_id = s_start.state_id
		where
		  s_stop.metadata_id = ( SELECT metadata_id FROM states_meta where entity_id = 'binary_sensor.lavastoviglie_operative_state' )
		  and s_stop.state = "off"
		  and s_start.state = "on"
		  and (s_stop.last_updated_ts - s_start.last_updated_ts) > 1800

	  column: "value"

and now I’m stuck again, while the configuration reload goes fine (so no definition error), the new sensor is not available to be selected in dashboard.
Any guess of what am I missing?

Thanks

3

Nicola,
I’ve done something kind of similar but in a different way. I want to know how many minutes my heat pump is generating hot water each day. So I have a counter that is incremented each minute the heat pump is generating hot water. I have a sensor that copies that value at 23:59:30 each day and a script that resets the counter. Then I can use the regular statistics view to see it over time. That doesn’t solve wanting an arbitrary window, but you can use it to get statistics for any timeframe, which you can then average over again. Maybe it will help!
-David

4

Hello David and thank you for your suggestion.
Something similar to your idea was one of my first attempt, but being a calculation on daily basis, I would have the conflict if I run the appliance twice or more in one day and in that case the total count duration would be a sum of all the events.

Right now the approach that is nearest to my goal is to use the sql query I wrote (the query itself works), but I’m still unable to display the sensor in a dashboard, the entity is not recognized

5

I finally figured out the issue, and it was the simpliest of the solutions… I did a full reboot and the sensor started to work. Also, only the query was displaying in dashboard the value as raw seconds, I added a time sensor to convert the format. This is the final structure:

template:
    - binary_sensor:
        - name: lavastoviglie_operative_state
          state: "{{ states('sensor.lavastoviglie_current_power') | float > 3.0 }}"
          device_class: power

    - sensor:
        - name: lavastoviglie_average_on
          state: "{{ states('sensor.lavastoviglie_average_on_seconds') | int | timestamp_custom('%H:%M', False) }}"

  sql:
    - name: lavastoviglie_average_on_seconds
      query: >
        select cast(avg(s_stop.last_updated_ts - s_start.last_updated_ts) as int) as avg_duration
        from states as s_stop
          left join states AS s_start ON s_stop.old_state_id = s_start.state_id
        where
          s_stop.metadata_id = ( SELECT metadata_id FROM states_meta where entity_id = 'binary_sensor.lavastoviglie_operative_state' )
          and s_stop.state = "off"
          and s_start.state = "on"
          and (s_stop.last_updated_ts - s_start.last_updated_ts) > 1800
      column: "avg_duration"

And it is working
image