Home Assistant's Influxdb (1.8 I think) and updating

1

I’ve been using their add-on for a long time with iotawatt (a energy monitor) and it works fine, and is easy to graph with Grafana.

I know relational databases, extremely unfamiliar with anything but the surface of Influxdb.

I want to be able to do one thing, and apologies that I may have the wrong terminology.

I have time series being updated with a circuit and kw (among other stuff), so things like GaragePanel at time was 23.4kw. Works fine.

But I’m getting sensors from other sources now that break down some of those, so I might get a GarageFan that is part of GaragePanel. Different source, comes in later and separately.

What I want to do is modify the data already stored so that the value of GaragePanel is reduced and a new row (?) at the time created, so that the total of GaragePanel and GarageFan equal what GargePanel was before (but now I have a breakdown).

From what I see, there’s no real way to use SQL to modify data in that manner. And at least some of what I read, I have to unload and reload the one already there?

Is that right, or am I just thinking about this incorrectly?

I come from the world that I can write sql statements to modify/insert/delete easily, but there seems to be a different philosophy and I confess I may not understand it. So apologies in advance… but do I understand correctly?

Linwood

2

Time series databases generally take the approach that when data is written, you likely want to retain that data as-is. Your database represents all data as it was generated in time, and modifying that data in place undermines that principle. Insertions are easy and highly performant, deletions can be done, but modifications are generally an anti-pattern.

What you can do, though, is process and write the curated data to a new/different table. So if you want to process your raw data, you could use, for example, continuous queries to move it into a new home where it’s formatted and behaving as you’d like.

3

Thank you. I have to think through that a bit notably the issue of timing, when I can assume that I have all the information to combine and make “final”. The deletion may help, I guess I can create that data so queries can access it, then at some point later modify it by deleting and re-adding. Will take some experimentation. Still struggling with the sort-of SQL statements that almost work the way I am used to except never quite. :slight_smile:

It does sound like what I was encountering was not my imagination but by design though. Thanks.