Possible to query the last time a light was 'on'?

[nldroid (Rob)]

The topic says it all: Is it possible to query the last time a light was ‘on’?

I want to create an automation that switches on a light but only if it wasn’t ‘on’ in the last three hours.

I currently created an automation that triggers when the light is switched off zo i can see the last time it was on by looking at the automation ast_triggered time but that seems a detour.

I’ve looked in the forum and Googled for info but i couldn’t find another way.

Is there any way of using light state information in automations?

[chairstacker]

I have a few automations that use the change of a switch or device_tracker state as a trigger to store the time it occured in an input_datetime.
That’s the easiest way I found so far.

Can’t see, why this shouldn’t work for light states as well.

[nickrout]

How about this sensor? https://www.home-assistant.io/components/sensor.history_stats/

[chairstacker]

As an example, this is what I use to record when the garden gate was last opened in an input_datetime:

# Info from ZWave Unit
- alias: Last Gate Opened Date and Time
  trigger:
    platform: state
    entity_id: sensor.gardengate
    from: 'off'
    to: 'on'
  action:
    - service: input_datetime.set_datetime
      data_template:
        entity_id: input_datetime.gardengate_rev_last_opened
        time: '{{ (as_timestamp(now()) | timestamp_custom("%H:%M:%S", true)) }}'
        date: '{{ (as_timestamp(now()) | timestamp_custom("%Y-%m-%d", true)) }}'

[nickrout]

Why, it is in the database?

[chairstacker]

Great!
What’s the way to get it out of there - I know basically NOTHING about how to do that

The link you provided refers to statistics and the examples there focus on ‘How much? How long? How often?’

And I’d have to keep my whole database for e.g. 10 days for all values while I only want to store it for my garden gate, right?

[nickrout]

That component will tell you how long the light has been on in the last 3 hours. If the answer is non zero then it has been on in the last 3 hours.

As for the database, use the api. The GET /api/history/period/<timestamp> should provide the required info.

[123 (Taras)]

The trick is getting it out of the database! I searched for “query history database” and most solutions end being either “no easy way to do it” or “use a python script”. That’s a shame.

Entity history querying in automations

Is there a way to query the history of an entity in an automation or script? For example, I know you can pull the last_changed from an entity but that will only be the immediate last attribute change. I know History has all the information and that you can try to directly access the database or restful API but it seems like it’d be useful to be able to access it within automations. I’m thinking it’d be useful to determine period of times between sensor states. In my use case I want to write …

Statistics, History Statistics, Averages & Power Measurement

Hi HA Community. I’m attempting to average power generation and consumption for my house (I’ve got a fronuis inverter with a modbus TCP link and I can pull in all the data). The problem is that the averaging functions in statistics only triggers on state change, so for example I’ve found that the average solar generation hangs (e.g. at 89.15W) due to the last state change to 0W when the inverter stopped producing power. It has been mentioned on a few other threads but I have not seen a solution…

Automation Condition using rain sensor value for the last certain period?

Nice investigation. I’m wondering if a custom component could be built to do an query and find the true last_changed on a sensor/device.

[nickrout]

there’s also https://www.home-assistant.io/components/sensor.sql/

[chairstacker]

Unfortunately, the ‘timestamp’ is what we’re looking for here, correct?
So, it’s hard to use it in the GET statement.

And as me - and many other people - have to delete or purge the database now & then I have taken even more ‘drastic measures’ to ensure that some of the values I rely on, e.g. precipitation for my automatic watering system, are stored completely outside the database.

For the precipitation info I store the values in a series of json files via a bash script:

#!/bin/bash 
#RUN AS /bin/bash /home/homeassistant/.homeassistant/bash_files/save_precip_info.sh
# In bash
# Step 1 - delete precip_yday-9.json
rm -v /home/homeassistant/.homeassistant/files/precip_yday-9.json

# Step 2 - rename precip_yday-8.json to precip_yday-9.json
mv -v /home/homeassistant/.homeassistant/files/precip_yday-8.json /home/homeassistant/.homeassistant/files/precip_yday-9.json

# Step 3 - rename precip_yday-7.json to precip_yday-8.json
mv -v /home/homeassistant/.homeassistant/files/precip_yday-7.json /home/homeassistant/.homeassistant/files/precip_yday-8.json

# Step 4 - rename precip_yday-6.json to precip_yday-7.json
mv -v /home/homeassistant/.homeassistant/files/precip_yday-6.json /home/homeassistant/.homeassistant/files/precip_yday-7.json

# Step 5 - rename precip_yday-5.json to precip_yday-6.json
mv -v /home/homeassistant/.homeassistant/files/precip_yday-5.json /home/homeassistant/.homeassistant/files/precip_yday-6.json

# Step 6 - rename precip_yday-4.json to precip_yday-5.json
mv -v /home/homeassistant/.homeassistant/files/precip_yday-4.json /home/homeassistant/.homeassistant/files/precip_yday-5.json

# Step 7 - rename precip_yday-3.json to precip_yday-4.json
mv -v /home/homeassistant/.homeassistant/files/precip_yday-3.json /home/homeassistant/.homeassistant/files/precip_yday-4.json

# Step 8 - rename precip_yday-2.json to precip_yday-3.json
mv -v /home/homeassistant/.homeassistant/files/precip_yday-2.json /home/homeassistant/.homeassistant/files/precip_yday-3.json

# Step 9 - rename precip_yday-1.json to precip_yday-2.json
mv -v /home/homeassistant/.homeassistant/files/precip_yday-1.json /home/homeassistant/.homeassistant/files/precip_yday-2.json

# Step 10 - rename precip_yday.json to precip_yday-1.json
mv -v /home/homeassistant/.homeassistant/files/precip_yday.json /home/homeassistant/.homeassistant/files/precip_yday-1.json

# Step 11 - Save Info from HAss
curl -k -H POST http://192.168.7.14:8123/api/states/sensor.pws_precip_today_metric > /home/homeassistant/.homeassistant/files/precip_yday.json

I agree, that might be extreme, but sometimes there is no one-size-fits-all solution.

I hope, though, that we’ve given the OP a few good ideas and they can choose what fits their need best.

[nickrout]

Yep lots of good ideas, and it goes to show that there is more than one way to do most things.

[123 (Taras)]

SQL Sensor?!? Well thanks a lot! Now I’m rummaging around home-assistant_v2.db with DB Browser and there goes the rest of my evening!

[nickrout]

If you go to the components section on home-assistant.io and look at the utility session there is some cool stuff.

[nldroid (Rob)]

Thanks for all the answers. I’m currently using this now:

- platform: history_stats
  name: Bol lamp on last hour
  entity_id: light.hue_ambiance_spot_1
  state: 'on'
  type: time
  end: '{{ now() }}'
  duration:
    hours: 3

The sensor shows me the number of minutes the light was on in the last three hours. Thats enough for me for using it in my automation.

[DavidFW1960 (David)]

I have an issue with limitless LED lights… sometimes they take 2 or 3 commands to switch on. So I use this automation (and a similar one for off)

- id: '1532478833426'
  alias: Lights Off Failsafe
  initial_state: 'on'
  trigger:
  - platform: state
    from: 'on'
    to: 'off'
    entity_id: light.lounge, light.dining, light.desk, light.office
  condition: []
  action:
  - delay: '00:00:02'
  - service: light.turn_off
    data_template:
      entity_id: >-
        {%- if (as_timestamp(now()) - as_timestamp(states.light.lounge.last_changed) < 5) and (as_timestamp(now()) - as_timestamp(states.light.desk.last_changed) < 5) -%}
        light.lounge, light.desk
        {%- elif (as_timestamp(now()) - as_timestamp(states.light.office.last_changed) < 5) and (as_timestamp(now()) - as_timestamp(states.light.desk.last_changed) < 5) -%}
        light.office, light.desk
        {%- elif (as_timestamp(now()) - as_timestamp(states.light.desk.last_changed) < 5) -%}
        light.desk
        {%- elif (as_timestamp(now()) - as_timestamp(states.light.office.last_changed) < 5) -%}
        light.office
        {%- elif (as_timestamp(now()) - as_timestamp(states.light.lounge.last_changed) < 5) -%}
        light.lounge
        {%- else -%}
        light.dining
        {%- endif -%}
  - delay: '00:00:02'
  - service: light.turn_off
    data_template:
      entity_id: >-
        {%- if (as_timestamp(now()) - as_timestamp(states.light.lounge.last_changed) < 10) and (as_timestamp(now()) - as_timestamp(states.light.desk.last_changed) < 10) -%}
        light.lounge, light.desk
        {%- elif (as_timestamp(now()) - as_timestamp(states.light.office.last_changed) < 10) and (as_timestamp(now()) - as_timestamp(states.light.desk.last_changed) < 10) -%}
        light.office, light.desk
        {%- elif (as_timestamp(now()) - as_timestamp(states.light.desk.last_changed) < 10) -%}
        light.desk
        {%- elif (as_timestamp(now()) - as_timestamp(states.light.office.last_changed) < 10) -%}
        light.office
        {%- elif (as_timestamp(now()) - as_timestamp(states.light.lounge.last_changed) < 10) -%}
        light.lounge
        {%- else -%}
        light.dining
        {%- endif -%}

Maybe you can use something similar…

[nldroid (Rob)]

Looks like i can use that indeed as a condition:

{{ (as_timestamp(now()) - as_timestamp(states.light.hue_ambiance_spot_1.last_changed)) < 10800 }}