Last Changed did not update properly, how to fix?

andyng · May 14, 2020, 7:56am

Hello,

I have setup my last_changed in my HA config, here is my home:

And the code:

thachnb85/home_assistant_config/blob/86a36aff7779f62fadaa829afa8ca9397028accb/sensor.yaml


- platform: broadlink
  host: !secret sensor_broadlink_ip
  mac: !secret sensor_broadlink_mac
  name: Home
  monitored_conditions:
    - 'temperature'

- platform: sql
  scan_interval: 2
  queries:
    - name: front door last opened
      query: "select last_changed from states where entity_id = 'binary_sensor.0x00158d000273bbe4_contact' and state = 'on' order by last_changed desc;"
      column: 'last_changed'
      value_template: "{{ as_timestamp(value~'+00:00') | timestamp_custom('%a %b %-d %-I:%M%p')}}"
    - name: living to garage door last opened
      query: "select last_changed from states where entity_id = 'binary_sensor.0x00158d000275ef5d_contact' and state = 'on' order by last_changed desc;"
      column: 'last_changed'
      value_template: "{{ as_timestamp(value~'+00:00') | timestamp_custom('%a %b %-d %-I:%M%p') }}"
    - name: garage door last opened

This file has been truncated. show original

But it doesn’t work all the time, sometimes working, sometimes not, any way to debug or check if last_changed was written properly to the database by Home Assistant?

Thank you.

VDRainer · May 14, 2020, 2:58pm

Use any sql client and look at the states table.

You will see that these rows are updated on every restart of HA.

A while ago i had the same problem and created a python shell script.

It fetches the last few states from an entity and returns the last_changed value if the state realy changed.

andyng · May 14, 2020, 6:19pm

Thanks @VDRainer

I checked the db, data is there, but it seems HA can’t query it, I ran the query manually inside sqlite3 and I am able to see the last_changed value.

andyng · May 14, 2020, 6:21pm

sqlite> select last_changed from states where entity_id = 'binary_sensor.0x00158d000273bb1d_contact' and state = 'on' order by last_changed desc;
2020-05-14 17:55:50.469958
2020-05-14 17:55:50.469958
2020-05-14 16:27:23.455198
2020-05-14 16:27:23.455198
2020-05-14 16:15:27.194371
2020-05-14 16:15:27.194371
2020-05-14 16:14:58.551793
2020-05-14 16:14:58.551793
sqlite>

This is one example.

I need to restart HA all the time to see last_changed updated on lovelace

I think I will need to subscribe to zigbee2mqtt directly, post the notification via firebase and update my own app, for the best update/stability