Calculate on time for switch - problems with timestamps

Configuration
, ,
#1

Hi All. I’m having some strange issues with timestamps I’m using to calculate how long a switch has been on for. I’m sure this is easy to solve but it’s got me stumped for now.

I’m setting a trigger-based sensor with a timestamp for the on-state, and the off state. Then calculating the difference between the two points to get number of elapsed seconds. Problem is that it seems like something is going wrong somewhere. For example - I time myself triggering the on and off states of the switch for 30 seconds (or 1 second either side of that) and I get the following timestamps.

On Timestamp: 1671411497.977381
Off Timestamp: 1671411499.400326

Diff between the two is ~1.422 - which means 1.422 seconds. Right?

So either HA is taking a long time to set the sensor values and I’m losing ~29 seconds somewhere due to latency or I’m reading this incorrectly.

Here is my trigger sensor:

- trigger:
    - platform: state
      entity_id: switch.wx8_107_zone
      to: "on"
  sensor:
    - name: "Zone_On"
      state: "{{ now().timestamp() }}"
      unit_of_measurement: "Seconds"
- trigger:
    - platform: state
      entity_id: switch.wx8_107_zone
      to: "off"
  sensor:
    - name: "Zone_Off"
      state: "{{ now().timestamp() }}"
      unit_of_measurement: "Seconds"

Example Calculation:

{{ (((states('sensor.Zone_Off')| float-states('sensor.Zone_On')| float))/60)|string + 'minutes'}}
#2

Can you describe what your end goal/ use case for this information is…? There are existing ways to access this information, but it would help to know what you are trying to do so we can suggest an appropriate method.

#3

Trying to give feedback to a dashboard as to how long a garden irrigation zone ran for. So I have a mushroom-template-card that displays how long the last run time was.

As an addition to my original question, after some more research I found this which says that the smallest interval history_stats can measure and is 36 seconds. Which is probably ok for my purposes but it feels like I should be able to improve on that.

#4

I can’t say what your issue is (yet). Those should update almost immediately, from my experience.

Could you set the following up to compare?

Make two input datetime helpers and set them with two automations. Let’s then compare the results.

What kind of switches are those?

Some things I can imagine: you turn it on, and while HA immediately sets the state as on, there is feedback from the device a little later to say: I’m really on! Check the sensor and switch timelines in your history panel. This would only be an issue if the switch briefly turned off in between.

You’re not using history stats here, so I don’t think it has anything to do with your issue, but it’s actually another thing you could try.

#5

Thanks @parautenbach I tried this and got near identical results. The weird thing in my mind is that if latency was the issue, you’d expect both timestamps to be delayed by a similar amount - and the resulting calc to be more or less accurate.

I did some more digging and found this post to get history in seconds (as opposed to a decimal of hours) but the solution won’t work now that history is integrated into core.

You post did help me though. The history panel seems to show accurate durations for on state.

image

So I just need to dig around to find out where I can extract that from. It doesn’t seem to be that accurate out of the history_stats platform.

#6

You could get it directly from the DB using a SQL sensor, bit part of me doesn’t want to go that route, because in principle it shouldn’t be necessary to go this route.

In your screenshot, are the times correct too, or only the duration?

#7

Thanks again @parautenbach, I think the times are correct. I’ll need to do some more testing when I can in the next few days but for the moment they seem much more solid.

For those stumbling along this post there are a few pieces involved to get this working.

  1. Install the SQL Lite Add-On to help with refining you SQL query
  2. You’ll need to create an SQL query that returns one value (at least I think) and then add that into the HA SQL integration as per SQL - Home Assistant
  3. Then you’ll need to query your DB for the right string. See below for my query - it returns the last time an event occurred as a datetime string with microseconds - which I will then convert to timestamo to do the calc. (at least that’s what I’m thinking right now).
SELECT Max( local_time)||"."|| date_decimals as state FROM (
SELECT *, 
"events"."time_fired" AS "UTC_Event_Time",
datetime("events"."time_fired",'localtime') as local_time,
substr("events"."time_fired", instr("events"."time_fired", '.') + 1) AS date_decimals
FROM "event_data" JOIN "events" On "event_data"."data_id" = "events"."data_id"
WHERE "shared_data" LIKE "%switch.wx8_102_veggies%" AND "shared_data" LIKE '%"service":"turn_off"%' AND "event_type"="call_service"
Order By UTC_Event_Time DESC
)