what about mariadb users ?
also can you please share your node/automation/sensor config
Wшер mariadb is the same. I have installed some time ago, but not yet used
1 Like
okay , what about config ?
i have not made it till now, but the way to make sensor from db is
- platform: sql
db_url: !secret db_url
queries:
- name: Hassio Last Stop Date Time
query: "SELECT DATE_ADD(time_fired, INTERVAL 1 HOUR) as time_fired FROM events WHERE event_type = 'homeassistant_stop' ORDER BY event_id DESC LIMIT 1;"
column: 'time_fired'
or with node red with node mysql
+1 on this issue. I’m sure it’ll be resolved eventually but I’ve found a pretty decent workaround for now for those few entities you want to save between reboots. For me, it’s the time since I last toggled the input boolean for replacing my cat’s litter box.
I’m using the custom component Variable from snarky-snark. Here’s the Github repo, though it’s in HACS.
configuration.yaml
input_boolean:
sifted_litter:
name: Litter Pan Sifted
icon: mdi:cat
Once the custom component var is loaded, we add that to configuration.yaml as well and have it look at the input boolean to store it.
configuration.yaml
var:
time_sift:
friendly_name: "Sift Time"
value_template: "{{ states.input_boolean.sifted_litter.last_changed }}"
tracked_entity_id:
- input_boolean.sifted_litter
var.time_sift only updates when the boolean changes, which it doesn’t between HA reboots.
Then just make a template sensor that compares the two.
configuration.yaml
sensor:
-platform: template
sensors:
time_since_last_sift:
friendly_name: Time Since Last Sift
entity_id: sensor.time
value_template: >-
{% set time = ((as_timestamp(now()) - as_timestamp(states.var.time_sift.state))) | round(0) %}
{% set minutes = ((time % 3600) / 60) | int %}
{% set hours = ((time % 86400) / 3600) | int %}
{% set days = (time / 86400) | int %}
{%- if time < 60 -%}
Less than a minute
{%- else -%}
{%- if days > 0 -%}
{%- if days == 1 -%}
1 day
{%- else -%}
{{ days }} days
{%- endif -%}
{%- endif -%}
{%- if hours > 0 -%}
{%- if days > 0 -%}
{{ ', ' }}
{%- endif -%}
{%- if hours == 1 -%}
1 hour
{%- else -%}
{{ hours }} hours
{%- endif -%}
{%- endif -%}
{%- if minutes > 0 -%}
{%- if days > 0 or hours > 0 -%}
{{ ', ' }}
{%- endif -%}
{%- if minutes == 1 -%}
1 minute
{%- else -%}
{{ minutes }} minutes
{%- endif -%}
{%- endif -%}
{%- endif -%}
This seems to work for me and persists across HA reboots. I also have an automation that just turns the input boolean back off as soon as it gets turned on.
1 Like
I still don’t get how this still could be an issue.
Facts:
- HA needs to be rebooted weekly to keep up with latest updates
- Yo need to reload the configuration (=restart) EVERY TIME you do anything which is not possible to do in the GUI, like basic stuff of adding a template sensor, a light group, etc. My average is around 30 restarts per day if I am actually working with HA.
Reasons for not making the last changed timestamp persistent mentioned:
-
You don’t need to restart, why do you keep restarting so often?Voided by facts above -
Something could have happened while HA restarts!
So what!? Then you have a last_changed which is 99% accurate (in case something happened during the 30s downtime during restart). It is better to have something that is 99% accurate than having something that is guaranteed 100% wrong!. I mean, saying it changed because of reboot is always wrong; no doors are opening because HA restarts! -
something might change during startupSame argument as previous point. -
Might break some existing logicSimple solution: simply introduce a last_changed_persistent attribute on all entities. Nothing will break.
If devs/admins are so sure that current behaviour of last_change is expected and wanted, simply introduce a last_changed_persistent attribute for all entities, wait a year, and see which attribute is used the most. I’m sure 99% of the community would prefer the last_changed_persistent one.
I mean, come on! I see 30+ row code solutions per entity above in this thread. Per entity. And to code all of that, you would need to restart the HA once more to make it apply and each time you add anything or remove anything, causing even more restarts. Having a persistant last_changed attribute is what everyone expect! It should be the default one. Perhaps add a
reset_all_my_timestamps_all_the_time: true
in the config if you actually WANT the current confusing behaviour?
16 Likes
You can add template sensors and groups etc without restarting simply by using the reload buttons in the config GUI. You can’t add standard sensors, camera and a few other things. When I create new automations in YAML I simply hit the reload automations button, done.
I do however agree that HA should keep it’s last known state change info across restarts. Even if the data is partly inaccurate due to the state of some entity having changed while HA was offline, it would be better than nothing.
1 Like
Yepp, that was exactly my point.
Which is also my point here. There are numerous of cases requiring you to do restarts all the time. And sometimes it is not super-clear what can be reloaded and what cannot; making me restart just to make sure… … and whoooops I had a water leak in the kitchen 2 min ago. Nahh, that was just HA restarting 
2 Likes
I too would like a last_changed_persistent attribute for all entities.
I found that Shaad’s solution above with input_datetime worked fine for me, but would still prefer a built-in solution as erik3 suggested.
automation:
- alias: Set change time
trigger:
platform: state
entity_id:
- switch.button1
- switch.button2
action:
- service: input_datetime.set_datetime
data_template:
entity_id: input_datetime.{{ trigger.to_state.object_id }}_last_change
datetime: '{{ now().timestamp() | timestamp_local }}'
input_datetime:
button1_last_change:
name: Switch last change
has_date: true
has_time: true
button2_last_change:
name: Switch last change
has_date: true
has_time: true
sensor:
- platform: template
sensors:
button1_last_change:
value_template: "{{states('sensor.time') and relative_time(strptime(states.input_datetime.button1_last_change.state[:19], '%Y-%m-%d %H:%M:%S'))}}"
button2_last_change:
value_template: "{{states('sensor.time') and relative_time(strptime(states.input_datetime.button2_last_change.state[:19], '%Y-%m-%d %H:%M:%S'))}}"
3 Likes
I have more than thousand entities right now.
Creating few more just to handle this… Let’s say I’m not a fan of that solution and would drown with templates and inputs…
The question is : for how many entities do you really need this info ?
I have about 700 entities, and I’ve identified only about 20 of them for which I really need a reliable ‘last_changed’ info.
But I full agree this would be way better if it was built-in and if reboot did not loose this info. 
It is not a matter of “how much”.
It is a matter of “how easy” (user friendly).
I started to count really fast in my mind to answer and… 20 is only device trackers/persons I’d track. Not even starting with switches, entities or states of sensors that are seasonal (and only i.e. summer-related). It would take months for me to add every each of this and add sensors around it just to solve something that could be done by database extra column.
I know what you mean - I only need it on a few entities for automations to work properly, but I would much prefer it to work with every entity for informational purposes (especially lights, motion detection, etc).
Here for example I make extensive use of last-changed state for my lights - I restarted HA an hour ago but they certainly didn’t turn on/off at that time which is annoying. Whilst I don’t need it here for HA to function, I would very much prefer them to have correct values.
This has been my number one annoyance with HA since I started using it a couple of years ago. I restart now and again for HACS updates and it’s extremely annoying to see all my last-changed statuses reset on the dashboard. Especially for some of the security sensors which throw me off now and again.
I know I could create workarounds but it just adds a lot more complexity and convoluted code. It would be great to either set the current last-changed to not reset on startup in the settings (not sure if technically possible) or to have a new last-changed attribute that persists through restarts.
Every new release I hope something is done to combat this, hopefully one day it will as I’d imagine this is quite a common use case for a lot of consumers! I don’t believe in the “This isn’t too much of an issue if you don’t restart” argument as to the average consumer/user you’d look at an attribute called “last-changed” and would expect it to be the last time that sensor changed, not that it changed due to a restart. Finding a solution to that seems better than just hoping people don’t need to restart often imo.
1 Like
Maybe introduction of new “Discussioncs” regarding Feature requests will trigger some recognition regarding this topic? As it is 2nd top-voted WhatTheHack idea… 
@frenck - how do you think? Will it fit?
1 Like
I think it will be part of the next release update!
At least I hope
What gives you that impression? Did I miss something?
- This is top x of the most requested WTH
- This just can’t wait any longer
