A follow up. I’ve been working off and on with influxdb and how to set things up. I am (for now) staying on the opposite course as @overeasy above, and sending circuit name as tags.
So what I did was design a set of retention policies for increasing levels of aggregation. For now I have:
name duration shardGroupDuration replicaN default
autogen 528h0m0s 168h0m0s 1 true
hourly 1800h0m0s 168h0m0s 1 false
daily 26400h0m0s 168h0m0s 1 false
That’s 22 days, 75 days and 100 days. So minute by minute data is saved for about 3 weeks (I’ll likely reduce that at some point). Note “autogen” is the Inflexdb name for “computer generated default”. You can change the default to something else, but this was just as easy, to use it (leave the corresponding field blank in the setup you get the default, and if you don’t change the default in Influxdb it is “autogen”).
That data is aggregated into hourly totals and kept for a bit over 2 months. In turn that is aggregated into daily data and kept for a bit over 3 years.
I then created continuous queries that looked more or less like these:
CREATE CONTINUOUS QUERY kWh_per_hour ON HA RESAMPLE EVERY 1h FOR 2d BEGIN SELECT integral(Watts, 1000h) AS kWh INTO HA.hourly.kWh FROM HA.autogen.Watts GROUP BY *, time(1h) END
CREATE CONTINUOUS QUERY kWh_per_day ON HA RESAMPLE EVERY 1d FOR 3d BEGIN SELECT sum(kWh) AS kWh INTO HA.daily.kWh FROM HA.hourly.kWh GROUP BY *, time(1d) TZ('America/New_York') END
The first does the hourly aggregation, the second the daily aggregation. It’s important to adjust the “tz” time zone as needed to get the right cutoff for days at midnight local.
This allows me to have different flavors of graphs, from short term very detailed ones (for 22 days) to moderate term hourly (2 months) to many year day-over-day comparisons.
Influxdb takes care of purging old data as needed based on the policies, and just changing the policy is all it takes if you want to keep some longer or shorter.
There are some quirks though, that are worth mentioning. InfluxDB does not let you schedule these queries beyond what you see. So when I say “every 1h” (1 hour) it runs at the top of the hour. There are two problems with that, (a) now all the data for that last minute may be there yet, in fact almost certainly is not, and (b) it does NOT summarize for the period ending. My guess is (b) is to allow for (a), but it means that you are always a period behind. So at 11:30am you have data through 9am, not through 10am; or on April 24 you have data through April 22, not April 23.
You can manually run the commands to bring it up to date, and these could be scripted outside of influxDB to run off schedule (e.g. 5 minutes after the time), but I have found no way to do it inside of InfluxDB (note the time shift functions also shift the periods).
Also note I have it resampling and overwriting data; this is to handle “normal” cases of missed data, e.g. network down, computer down, etc. IoTawatt will buffer, then dump several minutes, hours or even days at once; the resample will then merge it in. If it’s longer than the resample I just have to run the statement by hand (without a period selected it does all). If you didn’t know - Influxdb will only allow one row per timestamp per distinctive criteria (tags, etc.) so as new data comes in it does not try to figure out what was there, what wasn’t, it just recalculates everything and writes it – if already there and the same it stays the same, if different it updates (overwrites), if missing it creates.
Anyway… what I ended up with are graphs like these:
The top portion is (near) real time data for each circuit, plus temperature for reference.
The next two are recent data plots over time, and you can adjust the period (up to the maximum for the “autogen” or default data to show reasonably precise power (the Load Over Time) and since A/C is my biggest piece the A/C load by hour along with temperatures. This is good for the period of the grap.
Finally there are two fixed-length graphs at the bottom with daily energy usage and daily A/C usage.
The top real time gauges link to an analysis page for whatever you click on, e.g.
The above is my fridge. There’s probably a lot more I can add later to this one, e.g. the daily graph I can also put a dollars scale. I’ve been too fixated on the mechanics to think much about the analysis.
A clear issue in all this is cardinality and processing time as the data builds. The rollup and regular purge is intended to keep it manageable, but I will know only after I start getting at least the default and the hourly “full”.
Now what happens next is kind of interesting. I put them first into an iFrame in my Home Assistant dashboard. So for example:
I can click there and up comes the dashboard above. But I can also say “Hey google, activate show energy in bedroom”:
My LG TV calls up the Grafana graph and displays it.
Please do not ask my WHY I might need that, but it will really confuse visitors (it could be the Living Room or Den TV of course).
Incidentally that’s not just an image, it’s a live graph, and you can use the TV remote to select/change things like drill down or period covered.
I’m sure eventually I will find a good reason for it, in the meantime it’s kind of a cool solution in search of a problem.