Another take on Vacation lighting

Hi,

I made another take on the vacation or away lighting mode.

The concept I use is based on these steps:

  • Find the on/off historical status for lights/switches or any other entities that can be turned on/off for exactly 7 days earlier than now.
  • Turn these entities on/off correspondingly
  • Repeat every 5 minutes

Compared to other approaches:

  • One simple automation, no need for blue prints and automations per set of entities
  • Simple configuration centralized a couple of places
  • Easy to adjust according to which period you want to simulate and the precision of how often adjustments are done

Sound interesting?
Stay tuned, I’ll get back with the details :slight_smile:

Isn’t this exactly what presence simulation does?

Do you mean lighting?

Hi @frits1980 ,

Thank you for the notice.

I did not know the presence simulation integration, and yes it looks very similar.
I will examine it further and evaluate whether my approach has any further benifits otherwise I will recommend using the presence simulation instead.
I’ll get back

Corrected, thanks :slight_smile:

Here comes the step by step details

1. Get the historical values:
Basically I use an sql sensor like this:

- platform: sql
    queries:
      - name: light_entities_historically_on1
        query: "select group_concat(st.entity_id, ', ') as _out from states st
        inner join (
          select max(state_id) as max_id, entity_id from states 
          where last_updated < datetime('now','-7 days') and
          entity_id in ('light.gaestevaerelse', 
            'light.hue_color_lamp_1', 
            'light.sofa1spot', 
            'light.sofa2spot', 
            'light.hue_color_lamp_2', 
            'light.hyggespot', 
            'light.standerlampe', 
            'light.dagligdtue_pendel',
            'light.alrumspisebord',
            'light.kokkenbord1')
          group by entity_id) m_st
        on st.state_id = m_st.max_id 
        where state = 'on'"
        column: "_out"

This sensor returns those of the specifies entities that where on for exactly 7 days ago.
The output is one string holding a list of entities like this.

light.alrumspisebord, light.dagligdtue_pendel, light.hue_color_lamp_1, light.hue_color_lamp_2, light.kokkenbord1, light.sofa1spot, light.sofa2spot

I would have preferred that this sensor was defined generic that simply returns the entities that must be toggled, without the need of specifying exactly which entities to include, but due to:

  • The sql sensor does not support templates and hence dynamic SQL queries
  • A limitation in the sensor state value size that may not exceed 255 characters, ottherwise the sensor will fail.

Therefor I had to identify and select the entities to be included and group these to make sure that the maximum output (list of these entities with spaces and comas, as shown above) does not exceed 255 characters.

This resulted in defining 4 sensors:

  • light_entities_historically_on1
  • light_entities_historically_on2
  • light_entities_historically_off1
  • light_entities_historically_off2

Note You must adjust these to include the names of your entities to be included in the simulation and based on the number /length of the names of these entities you might need to define more or fewer sensors.

These are always up-to-date and hold of entities that where on and off for, the period i selected which is ,7 days, ago.

2. Define a helper that can be used to turn simulation on/off:

3. Define a binary sensor that determines when to activate the simulation:
I could have done that based on the helper I just defined, but I want to include another factor that the simulation is only activated if no body is home, so I defined these two binary sensor

- platform: template
    sensors:
      anybody_home:
        device_class: presence
        value_template: >
         {{ is_state('person.ghassan','my_home') or
            is_state('person.anne','my_home')}}
         
  - platform: template
    sensors:
      activate_vacation_light:
        device_class: presence
        value_template: >
         {{ is_state('binary_sensor.anybody_home','off') and
            is_state('input_boolean.vacation_light','on') }}

Note that I don’t use the default “home” zone as this is not adjustable and covers a larger area, which some might think is ok, but I preferred to define another zone “my_home” which is much smaller and covers only my property, this makes the detection of whether any body is home, much more precise.

So, the binary sensor activate_vacation_light is true if input_boolean.vacation_light helper is turned on, and no body is home.

4. Make the automation:
Here is my automation

alias: vacation light
description: ''
trigger:
  - platform: time_pattern
    minutes: /10
condition:
  - condition: state
    entity_id: binary_sensor.activate_vacation_light
    state: 'on'
action:
  - service: light.toggle
    data:
      entity_id: >-
        {%- for device in states.light | selectattr('entity_id','in', target_on
        ) |selectattr('state','in','off') | map(attribute="entity_id") | list + 
          states.light | selectattr('entity_id','in', target_off ) |selectattr('state','in','on') | map(attribute="entity_id") | list %}
          {%- if not loop.first %}, {% endif %}{{device}}
        {%- endfor  %}
variables:
  was_on1: '{{ states(''sensor.light_entities_historically_on1'') }}'
  was_on2: '{{ states(''sensor.light_entities_historically_on2'') }}'
  was_off1: '{{ states(''sensor.light_entities_historically_off1'') }}'
  was_off2: '{{ states(''sensor.light_entities_historically_off2'') }}'
  target_on: ( '{{was_on1}}' + ', ' + '{{ was_on2 }}' ).split(', ')
  target_off: ( '{{was_off1}}' + ', ' + '{{ was_off2 }}' ).split(', ')
mode: single

And here is some explanation:

  • The automation is triggered every 10 minutes:

    • You can adjust this to the precision period you like
  • The action is only performed if activate_vacation_light explained above, is true

  • Variables are defined to simplify the action:

    • target_on combines the values from light_entities_historically_on1 & light_entities_historically_on2 into one total list of entities that where on. Similarly for target_off.
  • The action toggles light entities that:

    • currently are on and are in the target_off list
    • and those that currently are off and are in the target_on list

And that is it.
This works perfectly for me :slight_smile:

If the SQL sensor gets more flexible in the future, I would love to make this more generic without the need of specifying the entities and worrying about the sensor size. Then the code can be used by every one without the need to change anything

Hope you get inspired and you are interested giving it a try.

Please let me know if you have any questions or challenges getting this to work

1 Like

Hi @frits1980 ,

The idea of “Presence simulation” custom component is very good, but it did not really work properly for me, as of it only makes the state change once or so. And I couldn’t figure out why.

Therefor I get ahead posting my solution.

Thanks for you input :slight_smile:

Kind regards,
Ghassan

maybe you can help with this error?

Error executing query select group_concat(st.entity_id, ‘, ‘) as _out from states st inner join ( select max(state_id) as max_id, entity_id from states where created < datetime(‘now’,’-5 days’) and entity_id in (‘light.0x00124b0021f1c1bb’, ‘switch.0x00158d0002e2dade_left’, ‘switch.0x00158d0002e2dade_right’, ‘light.kitchenstrip’, ‘light.livingroom_light’, ‘light.yeelight_3’) group by entity_id) m_st on st.state_id = m_st.max_id where state = ‘on’ LIMIT 1;: (sqlite3.OperationalError) no such column: created [SQL: select group_concat(st.entity_id, ‘, ‘) as _out from states st inner join ( select max(state_id) as max_id, entity_id from states where created < datetime(‘now’,’-5 days’) and entity_id in (‘light.0x00124b0021f1c1bb’, ‘switch.0x00158d0002e2dade_left’, ‘switch.0x00158d0002e2dade_right’, ‘light.kitchenstrip’, ‘light.livingroom_light’, ‘light.yeelight_3’) group by entity_id) m_st on st.state_id = m_st.max_id where state = ‘on’ LIMIT 1;] (Background on this error at: Error Messages — SQLAlchemy 1.4 Documentation)

I’ve used this app daemon tool for years to simulate behavior based on past activity when away from the house.

1 Like

Hi @shizlkazizl
I knew my setup didn’t work for a while but didn’t know why.
I finally took the time to examine it and found that the created column is now always Null and therefore I get nothing back from the DB looking for records where created is older than 7 days.
I now simply changed to use the last_updated column instead and it just works fine.

Hope this also works for you
.

Yup - this works well - SQL sensor is loaded into the UI these days (and actually it’s easier to just create it in there to be honest) - but your SQL works just fine.

Took a slightly different approach to the structure of the automation in the following ways:

  1. Rather than individual lights I used ‘room’ groups - means there is a lot less than 255 characters in the maximum SQL sensor state. I had the ‘rooms’ defined anyway so light.room_name was already around as a group of light.bulb1 light.bulb2 etc - the SQL can search for light.room_name instead of light.bulb1 light.bulb2 etc

  2. I created a script that sets the lights according to the SQL sensor as a one hit execution, then created an automation that calls the script on a time_pattern trigger - then disabled that automation.

  3. A second automation is triggered from the helper input_boolean transitioning state, and it toggles the first (repeating time_pattern) automation on or off. That way I can do other things in preparation for running and shutting down the vacation lights (like turn all the lights off afterwards for example) in this automation.

  4. It also means that in the dashboard you can show the input_boolean as a simple toggle, and also toggle its state from other automations (like a presence detecting automation) without having to worry about how it all sequences.

The only tweak I was looking to do that would make it worthwhile going to the bulb level is to get the HS and brightness values out of the state table then I can throw them together as a dictionary and pass them through to a scene activation to get exactly the same light pattern (rather than just bulb on and off) - but to be honest given it’s just about making the lights change when I’m not there I’m not 100% sure it’s anything more than an interesting exercise.

Have to say this is the best way I’ve found of doing vacation lighting - it’s just really really simple and doesn’t clutter up HA with a pile of shadow entities (which I’ve seen in other approaches).

Oh one other tweak, given the SQL sensor updates all the time (not that great a load) it should be relatively easy to just tell HA to not update it by poll (just a toggle option on the integration for the on and off SQL sensors), and get the automation to force an update of the sensor every time it needs to get the settings - I’ll play with that tonight.

Just to follow up. Sensors can easily be turned off in sql integration ui. Then scripts just call homeassistant.update_sensor when they need to access the historic lighting data - zero load on ha unless the vacation lighting is running. Perfect.

Given the limitations of getting the return result from an SQL sensor into HA (first row, max 255 chars, remaining columns as attributed) - it’s pretty tricky to pull out the individual light settings to replay them in HA - just the sheer volume of data - but in case anyone gets here and has some better thoughts - the SQL to get the data out of the HA database:

select s1.entity_id, s1.state, state_attributes.shared_attrs as on_lights
from states s1
inner join
(
	select max(state_id) as max_id, entity_id, attributes_id from states
	where last_updated < datetime('now','-7 days') and entity_id like 'light.%'
	group by entity_id
) s2 on s1.state_id = s2.max_id 
inner join state_attributes on state_attributes.attributes_id = s2.attributes_id
where state = 'on' and not instr(shared_attrs, 'group') and not instr(shared_attrs, 'entity_id');

Yet another change in HA DB has broken the sql sensor and requires new updates.

Now last_updated is not populated anymore, instead it is last_updated_ts which is a time stamp.
Hence
last_updated mus be replaced by datetime(last_updated_ts,‘unixepoch’) in the SQL sensor code to get it up and running again.