Washing machine cycles counter on past power data

I have a power meter attached to the washing machine and I can pretty much well detect when cycle completed. This is not a question how to manage this part.

I also have a history data for the washing machine for about 50 cycles and I was wondering how to create a counter helper and align the states changes timestamp to the history when history value transitions from >0W to =0W.

I don’t know if HA has a way to scan the history and perform sensor state changes based on history data - I suspect, not. So I did SQLite manipulation on my own, which worked well, but only to realize this morning that counter data is lost.

So what I did is the following.

Creation of a new counter helper

  • Create a new helper counter counter.washing_machine_cycles
  • Clicked Increment about 50 times to log all states history from 0 to 50. This has successfully insert the entries into the states table, together with the metadata_id in the states_meta table.

Export the historical data from statistics for power measurement

  • Find the meta id for the washing machine power for statistics
  • Read the historical entries from statistics table that match meta
  • Filter out only the values that are above 0 and have a delta time between them minimum 2 hours. I used python to verify the data

Finally, I’ve updated the states table for my counter.washing_machine_cycles, each increment state matches the time when power measurement went from non-zero to zero. This happened for 12 months of data in total.

During the update, history graph looked nice. The counter went up on the history chart in a consistent cumulative way, every time there was a spike in the power measurement chart.

But then this morning I realized that history chart doesn’t show the data for anything beyond 10 days. My assumption is it is because of the long-term statistics and the fact that I’ve updated the states table with data more than 10 days far, which is default history.

I’ve checked the SQLite raw data with the Web addon and realized that:

  • HA has deleted the entries from the states table for anything beyond the 10 days
  • HA did not transfer the data to the long term statistics table

Or simply put, is there a recommended way to run the past historical data and set the sensor state(s) based on the history data? I simply want to show the monthly granularity chart of the cycles count.