Timestamp of when a sensor switches to specific state

I’m trying to measure the start and end of power outages at my home. I have the UPS sensor setup properly.

I’m trying, to no avail, to create a template or history states sensor that shows a timestamp for when my UPS switches to specific states.

sensor.ups_status_data states:
“DISCHRG” power is out, UPS is discharging.
“CHRG” power is restored, UPS is charging.
“OL” power is connected, UPS is charged.

I’d like to create two sensors:
Last Power Outage: Date & time
(Date & time that sensor.ups_status_data became DISCHRG)

Last Power Restoration: Date & time
(Date & time that sensor.ups_status_data became CHRG)

Any advice for creating a sensor that is set to the last time a sensor switched to a specific state?

I recommend creating an input datetime sensor for each one of the times you want to capture, i.e.

input_datetime:
  gardengate_last_opened:
    name: Garden Gate Last Opened
    has_date: true
    has_time: true

Then you can populate them whenever the change to or from this state occurred in an automation:

- 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_last_opened
        time: '{{ (as_timestamp(now()) | timestamp_custom("%H:%M:%S", true)) }}'
        date: '{{ (as_timestamp(now()) | timestamp_custom("%Y-%m-%d", true)) }}'

Thanks @chairstacker, I used this approach for a week, until I started using a SQL Sensor:

sensor:
  - platform: sql
    scan_interval: 10
    queries:
      - name: Last Power Outage
        query: "SELECT * FROM states WHERE entity_id='sensor.ups_status_data' AND state='DISCHRG' GROUP BY state ORDER BY last_changed DESC LIMIT 1;"
        column: 'last_changed'
        value_template: "{{ as_timestamp(value + 'Z') | timestamp_custom('%a %b %-d %-I:%M%p') }}"
      - name: Last Power Restoral
        query: "SELECT * FROM states WHERE entity_id='sensor.ups_status_data' AND state='OL CHRG' GROUP BY state ORDER BY last_changed DESC LIMIT 1;"
        column: 'last_changed'
        value_template: "{{ as_timestamp(value + 'Z') | timestamp_custom('%a %b %-d %-I:%M%p') }}"

I like this approach as it uses the database values for when these events occurred, rather than creating new tracking sensors and using automation.

If anyone finds this and is curious about the + ‘Z’ in my value_template, it’s because the timestamps are stored in the DB as UTC, but when the queried date values don’t indicate that. Adding the Z tells as_timestamp that the source date is in Zulu time/UTC.

1 Like

This sounds like a good idea @sirris101 - I might switch to this myself. :+1:

What happens, though, in case the last power outage happened earlier than your history/recorder goes back to?
Does it keep the date and time or does it just change to ‘unknown’?

Great question. So I just tested this, if HA starts and the query returns nothing, the field will be blank, and not show what the previous value is, unfortunately.

1 Like

Thanks for the update @sirris101 - I have had that happen even with the input_datetime approach, e.g. when HA didn’t shut down properly, but only very infrequently.

For now, I think I’ll stick with my approach then.