How to Get Last State Changed from Switch?

Hi all,

I’ve been looking around on how to get the last timestamp a switch is turned on but still not get satisfying answer. Several threads here tried to get last_changed state from the sensor. But I’m trying to get one from a switch entity. I tried to adopt it to my sensor template like below, but I don’t get an updated time when I turn on my switch. Moreover, the sensor template time is always resetted to the time my HA booted.

  - platform: template
    sensors:
      last_fish_feed_time:
        friendly_name: "Last Fish Feeding Time"
        value_template: "{{as_timestamp(states.switch.turn_on_fish_feeder.last_changed) | timestamp_custom('%A %d-%b-%y, %H:%M:%S')}}"

For a little background, I’ve built a custom fish feeder using ESP8266 with esphome firmware. The esphome integrates very well with HA and creates a switch for me so I can manually turn on the feeder from HA. The problem is there are several ways to turn on the feeder :

  1. Using physical button on the feeder (which will pass switch.turn_on command to HA)
  2. Using HA frontend where I have the switch entity
  3. HA automation where I set the feeder switch to turn-on automatically at 6AM and 6PM

I want to know the last time the switch is turned on (from any way above) to make sure I’m not over or under feeding the fish. Thank you for all your help…

What you have written is correct but HA needs to know what to monitor for updates. For that you need to supply an entity id to monitor. Try this:

- platform: template
    sensors:
      last_fish_feed_time:
        entity_id: switch.turn_on_fish_feeder # Entity to monitor for changes
        friendly_name: "Last Fish Feeding Time"
        value_template: "{{as_timestamp(states.switch.turn_on_fish_feeder.last_changed) | timestamp_custom('%A %d-%b-%y, %H:%M:%S')}}"

I think that was the missing bit… :wink:

Thank you, I’ll try and report back…

Pretty sure that HA will always report the last state changed as the last boot time. It is part of the way that state machine works.

I have worked around this by publishing the timestamp to a MQTT topic with persistent flag set each time the state actually changes. This way it preserves it during a HA restart.

1 Like

Good idea! So you use the mqtt topic as a sort of variable to store the time that persist between HA reboot? Could you please share that bit of code here? Would love to take a look, and I think I can add some codes to publish mqtt in esphome codes…

@aheath, that’s clever!

Here’s a python command_line script that i use to get the real last_change directly from the MySQL DB.
Maybe another approach.

Example is that I have a camera sitting over my pool chlorinator. An automation runs every 30 minutes during daylight hours that calls a script to publish retained message to an MQTT topic and take the snapshot.

script.yaml

take_pool_chlorinator_snapshot:
  alias: Take Pool Chlorinator snapshot
  sequence:
  - service: mqtt.publish
    data_template:
      topic: "pool/lastsnapshot"
      payload_template: "{{ now() }}"
      retain: true
  - service: camera.snapshot
    data:
      entity_id: camera.pool_controller
      filename: /share/pc1.jpg

I then have a sensor that grabs the last time it was ran from the MQTT topic, and another one to format the date suitable for display.

sensors.yaml

- platform: mqtt
  state_topic: "pool/lastsnapshot"
  name: "Last Pool Chlorinator Snapshot Raw"
  force_update: true

- platform: template
  sensors:
    last_pool_chlorinator_snapshot:
      entity_id: sensor.last_pool_chlorinator_snapshot_raw
      value_template: >-
        {% if as_timestamp(states('sensor.last_pool_chlorinator_snapshot_raw')) | timestamp_custom('%Y-%m-%d') == as_timestamp(now()) | timestamp_custom('%Y-%m-%d') %}
          {{ as_timestamp(states('sensor.last_pool_chlorinator_snapshot_raw')) | timestamp_custom('%-I:%M%p') | lower }}
        {% elif as_timestamp(states('sensor.last_pool_chlorinator_snapshot_raw')) | timestamp_custom('%Y-%m-%d') == (as_timestamp(now()) - 86400) | timestamp_custom('%Y-%m-%d', True) %}
          {{ as_timestamp(states('sensor.last_pool_chlorinator_snapshot_raw')) | timestamp_custom('%-I:%M%p') | lower }} Yesterday
        {% else %}
          {{ as_timestamp(states('sensor.last_pool_chlorinator_snapshot_raw')) | timestamp_custom('%-I:%M%p %d/%m') | lower }}
        {% endif %}

I also have 6 Bruh multi-sensors that I built and I use very similar automations to keep track of when the last motion was detected on each of them.

I do a nightly HA restart just after midnight to work around an issue with some Broadlink switches I have. This restart forces the MQTT connections to refresh from the last retained message in the topic, so the “yesterday” logic is automatically handled by that. If you don’t do a nightly restart then you would probably need an automation to do this for you (not sure exactly how though)

1 Like

… and if it is easier, you could create a SQL sensor to query the HA database directly.

sensor:
  - platform: sql
    queries:
      - name: Real Last Update
        query: "SELECT state FROM states WHERE entity_id = 'sensor.name' ORDER BY created DESC LIMIT 1;"
        column: 'state'

replacing sensor.name with the name of your sensor

I am not 100% certain this will work, but give it a try and see how it goes!

I steer clear of direct SQL queries, but to be honest, I have no idea why :flushed: