Reliable Option for Home Assistant History/Long-Term Statistics

1

I’m working on a project and would like to gauge community interest.

Briefly, it’s a Home Assistant App (add-on) that fixes many the common HA pain points:

  • Missing history during HA reboots or communication dropouts.
  • Inability to have use outputs as HA sensors.
  • Inability to see 5-second granularity data.
  • Inability to automatically import history and long-term statistics into HA.

In a nutshell, the app automatically configures the influx uploader to send the necessary buckets from all inputs (and outputs) at a 5-second post interval (or whatever post interval you like) starting at any available historical date you choose, then records that data for use within HA. It essentially reconstructs the IoTaWatt log fields in a local database. (This is trickier than it sounds.)

Once the uploader catches up, the data also populates the same W/WH and V/hZ sensors as the existing HA integration, as well as any outputs (including ones that use integrators) configured on the IoTaWatt and keeps those updated as sensors as well.

Any time the app restarts, recovers from a lapse in the uploader posts, or any time HA restarts, the app will attempt to fill in any missing history in HA.

The app supports multiple IoTaWatts. (I have 400A service, so I have 2 panels and 2 IoTaWatts.)

For the first version, the app acts as an IoTaWatt to MQTT bridge, which means you don’t need a separate integration if you already have MQTT. In the future I might develop a companion app or create a PR to allow the core integration to pull data from the app rather than directly from the IoTaWatt(s).

There’s more, but this is already a long post - so the rest is some background on how the project came to be.

The history of the IoTaWatt/HA integration is somewhat long and complex. There are fundamental incompatibilities between the HA and IoTaWatt approaches to handling data that can’t be resolved by an integration alone. HA sensors are always “immediate” - so you lose history during the time HA reboots or if there is a connectivity issue with the IoTaWatt. Coming the other way, there is no “unique ID” for IoTaWatt outputs, so they are not eligible to be a device entity in HA. Both of these have been fiercely argued in numerous forums, and the compromise is what we have today. Fundamentally, HA doesn’t consider itself a historian. It’s a recorder. The HA devs have added APIs to allow for modifying history and long-term statistics, but nothing in the core IoTaWatt integration that brings it full circle.

It’s frustrating because all of the granular data is faithfully recorded in the IoTaWatt log for a whole year, and less granular long-term statistics for an additional year - but the standard integration is useless for dealing with this.

There are other problems. It’s common to encounter HTTP exceptions in the HA logs whenever the integration times out on a query, which in my case is relatively common for one of my IoTaWatts that doesn’t have a perfect signal. The responses can be slow, creating a choppy sampling set. It’s also not possible to get 5-second granular data for inputs/outputs using the core integration. I’ve played with changing the integration to support this, and it’s a disaster with timeouts and seems to even cause spurious IoTaWatt reboots.

You can “fix” some of this using a combination of Ingress or EMonCMS and various tools and tricks to import the data into HA - nothing fully automatic and self-contained exists.

So, with some free time and an abundance of frustration with the limitations of the existing system, I started working on this project. I have been through several re-designs as I learned more about how both IoTaWatt and HA do things internally.

I’ve mostly wrangled it down to a well-behaved HA app (or docker container if you prefer) that uses very few resources, as little storage space as possible, and handles most of the corner cases I see with my system.

I still have some wrinkles to iron out before I would consider publishing, but if there’s enough interest, I will continue ironing and do the extra work to push to a public repository and publish as a Home Assistant app.

I’m also open to criticism and to answer questions if anyone is curious about the details.

1 Like
2

None of those are actually issues I have encountered.

  1. I use a UPS to power my network equipment, home assistant and iotawatt. After a (rare) home assistant restart or reboot all energy sensors are restored. There may be small gaps in the power sensor data but that has never been an issue for me.

  2. Outputs do create sensors, just not currently associated with the device. This may change due to recent device updates that are currently under beta testing. During testing of the latest core beta version it was found that the iotawatt integration generates a warning that device ids are required for the sensors. Something that the HA devs have refused to allow in the past. They are aware of this catch 22 but I have no idea how they will solve it. They have 12 months before it becomes a problem.

  3. 5 second data is not required. Integration happens in the iotawatt. At a much quicker rate than that then the energy total is presented to home assistant at a reasonable interval (10 sec). It would just be needless network traffic.

  4. Not sure what you are getting at here. All the sensors support long term statistics. If you mean for data stored in the iotawatt before it was connected to home assistant then that has never been a requirement for me.

It is not common to suffer HTTP timeouts either. That is a problem with your network you should fix.

However, having said all that I would be interested in checking out your app/add-on as long as it is not vibe coded.

3

Hi Wonko,

Thanks very much for working on this. I too have been frustrated by the immediate nature of HA inputs. I presently have my Iotawatt feeding the HA emoncms add-on, and then I access that emoncms data from HA.

I like the idea of an IoTaWatt to MQTT bridge, followed by an MQTT to HA integration that supports backfilling data. I find HA’s integration with MQTT very powerful, especially using HA autodiscovery features.

I like MQTT messages more than API calls, as MQTT has visibility for messages built in.

If your MQTT to HA integration has a provisioning / mapping interface that maps a device id or channel name or number to an HA identifier, that would be useful. Sometimes you want to move a device or change a channel name while keeping the same HA identifier (device replacement), other times you want to change the HA identifier for an unchanged device because the monitored area has changed occupancy or use. If these details can be changed and validated ahead of time, rather than exactly when the physical device is changed, that is helpful.

I’d be happy to try integrating with what you have, and see how the system could be generalized to support my use cases.

Thanks!

4

Contrarian response: I’ve always used Influxdb with Grafana and ignored HA as an energy reporting platform, and I still think that’s best. The HA approach to statistics vs history, to graphing that is subject to the vagaries of each new release bothered me. What I did want was the parent/child relationship of measurement points.

When Home Assistant apparently casually and suddenly obsoleted Influxdb (and better be careful or your database goes away with one click), I migrated to a separate (not inside Home Assistant) instance of Postgresql. You can use timescaledb with it, though I did not. I just finished yesterday loading about 18 months worth of history from iotawatt, and have it polling every minute to pull the latest minute (or more if it needs to catch up). I also can poll HA energy sensors, and have it adjusting measurements in a parent/child relationship like HA.

Then graphing is easy with Grafana, and I am in control over my historical records, not Ha.

I personally think Iotawatt was a hugely mature and well thought out product, and HA’s energy was (and to an extent still is) a rapidly changing environment. It’s sort of a tossup whether it’s worth diving into HA’s analysis now or not, IMO.

But the API for pulling data from IoTawatt is rock solid (just don’t pull huge volumes at once, I decided 1m was more than adequate as an interval, and I pulled about 200m at a time for history, takes about 1s for each pull during which it may not be sampling values. For a short pull it’s far shorter (but it’s actually better to pull 1m every 10m or so rather than every 1m).

5

Thanks for the reinforcement of the IoTaWatt architecture. Like much of the software I’ve left behind over the years, it is surviving the test of time.

I locked horns with the HA folks when they imposed arbitrary restrictions on the user developed IoTaWatt integration and I refused to complicate the IotaWatt architecture to meet their demands.

6

I generate a hash from the IoTaWatt “script” - so it doesn’t care about the input names, but it does care about the math on outputs, which arguably it should - since that’s a change to the formula used to create it.

7

Yeah, I don’t get what was so difficult about the whole thing to be honest. The API allows plenty of discovery to create unique IDs for each input/output. I was able to discover everything in the API and then poke the necessary scripts into the influx uploader quite easily. I was also able to create a unique ID for each MQTT entity by hashing the input/output scripts and the MACs of the IoTaWatts. The integration could have done the same thing to solve the unique ID requirement.

Your circular logs are super efficient, clean, and very easy to work with once you understand how they work. It’s a compact, elegant solution to keeping lots of raw data acquisition locally. I had to poke through the code a bit to figure it out, but I cut my teeth on C back in the day, so it wasn’t too difficult. It would actually be nice to have an API or uploader to get the logs directly. I essentially recreate them from the influx uploader, which feels like kicking a dead whale down the beach, but it’s the best way to ensure my math always matches the IoTaWatt. You already do the “hard part”, which is keeping up with the cursor and catching up after any sort of communication interruption. To query, I just re-implement the scripts to run against the local logs to get whatever information I need. This way I can quickly regenerate history from a compact local source at any granularity basically the same way the IoTaWatt data view does. It’s also nice that I can immediately support new outputs without having to download anything new. Integrators are another story. I don’t have solar, so I haven’t gotten around to implementing everything for them yet.

One thing I actually discovered I really like is having the data arrive via MQTT. I know you’re not really working on it anymore, but a native MQTT option for IoTaWatt would be really slick. Do you think there is enough RAM to support it? MQTT is pretty lightweight. Would you be willing to consider a PR to add it to the firmware? Would you be willing to consider a PR for a “raw” uploader that sends fixed-length log records and maintains a cursor?

8

I mainly use it for automations and notifications, but HA also does a nice job of tracking TOU rates with its energy meters. It’s also nice to be able to create simple dashboards to show current and historical usage by area/appliance/vehicle in context. Grafana is great for overall dashboarding of energy stuff, but being able to combine energy consumption with everything else in HA has a good bit of additional value for me.

9

Awhile age someone posted that they have a python program to rip data from an IoTaWatt data file. You can copy the SD in a minute or two and then use something like that to regott egg mat to your liking. No matter how hard you try, the SD interface in the IoTaWatt will take a looooong time to read the entire datalog and send it over WiFi.

Adding an MQTT capability to the firmware is not possible. What some others have done is use node-RED to extract the data (via Query, status API or an uploader) and send it via MQTT. Real simple.

10

To the HomeAssistant vs other analyzer/database, I thought I would share what happened. I pulled IoTaWatt into Postgresql (and do so every minute), but I had a dozen or so energy measurement switches (and similar), so I separately pulled HA’s Power and Energy sensors in also.

And discovered HA energy is every-increasing values, so you have to do a difference. That makes a certain amount of sense since you do not know what you do not know in intervals between polls.

All good and I started seeing these huge negatives in my energy. Come to find out HA is sloppy about what it means. An every increasing sensor indicates it reset by … well, not increasing. So my collector assumed that if point N had a value greater than point N+1 that point N+1 was a reset. You don’t know how much you lost, but you know the value of point N+1 is at least the total during the interval.

Well… guess what – they are sloppy, really sloppy. Their documentation says assume it’s a reset only if it’s at least 10% less than the last point. Huh— so it can go negative (we are not talking here about actual in/out power flow like solar).

Except they are wrong – it may (or may not) be correct for an HA integrated power figure, but others like ESPHOME have their own rules (esphome appears to be literally anything less). Who knows what Shelly does. Sigh…

I ended up pulling all my energy sensors out and just multiplying by the interval, because HA’s API is too ill defined to be accurate across all the various always increasing sensors.

That’s what you get with a system designed to show data in flashy graphs first, and figure out how to accurately derive data second.

11

IoTaWatt integrators solve that problem accurately.

12

Absolutely. The only issue I had loading historical data from IoTaWatt was that I had moved from CT’s around over the last couple years and did not keep careful track what went where, so I was a bit limited beyond certain points in time to detailed data.

What merging HA data into IoTaWatt data did, though, that was useful was allow me to create parent/child relationships on measurement points, so every subpanel is a child of my main panel (those from IoTaWatts), but things like landscape lights (from a HA smart outlet) become a child of an IoTaWatt branch circuit, same with three UPS’s for computer gear. I subtract power and energy of the child from the parent leaving a residual which is “unmonitored devices” (plus/minus measurement error). So I get a deeper look into home device usage than individual branch circuit monitoring can provide.

But I spent 10 times the time debugging messy data and vague definitions (like that reset) with HA data than I did IoTaWatt data.

But to the original topic – I don’t consider HA itself a “reliable option” for long term energy data. Something like Postgresql is not going away (it’s becoming more widely used all the time), it’s free, it’s readily supported by Grafana and other analytical tools, and it has a TimescaleDB add on if you want it.

1 Like
13

I have a version of IoTaWatt firmware that supports a postgres uploader. Unfortunately the original contributor lost interest just before final testing. Is this of interest to anyone else in the community?

14

Hmmm… I’m tempted to say yes, but at the moment my pull-type collector is working fine with iotawatt, so I am a bit reluctant to rock the boat. Now if You had mentioned that 2 weeks ago…

Hopefully someone else will chime in. I’m very happy with Postgresql as a back end, it’s a lot better than influxdb was.