Average-time-something-happened-sensor?

Configuration
1

Hi!
I’m looking for a way to create a kind of sensor calculating the average time of the day an event happened in my house for the last x days.

Background:
I built “in bed” sensors, which turn on night-mode (very dim lights). With a newborn in house, it would be very handy to turn on this night mode about 30 minutes before we usually go to bed to not wake her up if she’s sleeping already. I could look into the last days and make the time fixed, but:

  • times differ between weekdays and weekend
  • times shift during the year

What I need

  • sensors spitting me out the average time binary_sensor.night_mode switched to on for the last 10 weekdays / 5 weekend days / 5 mondays…

What I tried to do (did not work)

  • use statistics sensor (does not take input_datetime)
  • use filter sensor (does not take input_datetime)

What I think could be working (but seems very cumbersome to do)

  • convert input_datetime to a sensor with decimal numbers (20:30 would be 20,5), apply filter sensor with moving average and convert it back to a time
  • create an array of input_datetime sensors (weekday_time_1, weekday_time_n, […] weekday_time_10), write the first time-change to 1, on every time-change move existing value to n+1 and the new value to 1, then calculate average of sensor_n=1 to sensor_n=10 using templating (somehow); Do the same for weekends

What I need you for:

  • There must be an easier way of achieving this. :grinning: I just can’t believe I’ m the only person feeling the need of using past data / your usual routines to automate things in the house.

So, any ideas?

1 Like
2

Use an sql sensor to query the database for the last 7 days?

Or in the action of the automation, set a sensor to the time it switched on?

3

Wow, that SQL sensor uses a whole new syntax to learn!
Thanks, gonna dig into that direction!

4

Did you find a solution to this?

I’ve been playing around with the SQL sensor… When I run the following query in the SQLite Web it prints out the unique values from the Input datetime.

SELECT DISTINCT (s.`state`)
 FROM states s 
 INNER JOIN 
   (SELECT state_id 
   FROM states 
   WHERE entity_id='input_datetime.a_home_from_work' 
   AND state <> 'unavailable' 
   ORDER BY state_id DESC LIMIT 50) 
 x ON x.state_id = s.state_id

However, the SQL sensor only returns the final value and I haven’t found a away to output an array those values.

UPDATE: I’m sure there is a better way to do this, but this is what I cobbled together with my limited SQL knowledge

By using multiple SQL sensors , each with a defined limit, you can then use a template sensor to find the average.

SELECT DISTINCT (s.`state`)
 FROM states s 
 INNER JOIN 
   (SELECT state_id 
   FROM states 
   WHERE entity_id='input_datetime.amanda_home_from_work' 
   AND state <> 'unavailable' 
   ORDER BY state_id DESC LIMIT 50) 
 x ON x.state_id = s.state_id LIMIT 1

For subsequent sensors just increment the number after the final LIMIT.

To make getting the average easier I added the following value template to each of the SQL sensors

{{ as_timestamp("1970-01-01 "~value|string~"-00:00")}}

The template sensor for the average time is as follows:

template:
  - sensor:
    - name: "Get Home Timestamp"
      unique_id: "8153807397056722843"
      state: >
        {% set avg = [ 
        states("sensor.a_home_n_0")|int,
        states("sensor.a_home_n_1")|int,
        states("sensor.a_home_n_2")|int,
        states("sensor.a_home_n_3")|int,
        states("sensor.a_home_n_4")|int,
        states("sensor.a_home_n_5")|int,
        states("sensor.a_home_n_6")|int,
        states("sensor.a_home_n_7")|int] 
        | unique | average %}
        {{ (avg | as_datetime).strftime("%H:%M:%S") }}

Alternatively, you can put the sensors in a group and base the template sensor off the group.

Group-based example 
      - name: "Home Mean Time"
        unique_id: "8672280739387015543"
        state: >
          {{ (this.attributes.timestamps|average|as_datetime).strftime("%H:%M:%S") }}
        availability: >
          {{ expand('group.amanda_home_times')
          | rejectattr('state', 'in', ['unavailable', 'unknown', 'NaN'])
          | list | count > 0 }}
        attributes:
          earliest: >
            {{ (this.attributes.timestamps 
            | sort | list | first | as_datetime).strftime("%H:%M:%S") }}
          latest: >
            {{ (this.attributes.timestamps
            | sort | list | last | as_datetime).strftime("%H:%M:%S") }}
          timestamps: >
            {{ expand('group.amanda_home_times')
            | rejectattr('state', 'in', ['unavailable', 'unknown', 'NaN'])
            | map(attribute='state') | map('int', 0)| unique | list }}
1 Like
5

Wow, great stuff!