Hi,
I use JDBC binding to store power status of my gas heating (on/off, RPM of fan, and other non-continuous states). To save space in db, I would like to avoid interval storing. The persist strategy everyChange seems a good fit.
However, for charting purposes it would help if not only the changed value is stored, but also the last known value before the change:
That is, there should always be two neighbouring records for each particular value.
I assume I could derive the missing ālast known value before changeā on-the-fly when the chart is generated, since I can assume that the sensor reading takes place e.g. each 5 minutes. But for the simplicity I would prefer to have that directly in the persistence database.
I donāt have an answer but wanted to pipe up to say I have been chasing the same concept. Perhaps the issue is charts need a tweak?
Filling the database with cron based updates makes no sense and is only as accurate as your interval.
To me it seems the database already has the necessary and correct info. Adding duplicate values all the time or somehow managing to jamb the previous state milliseconds before the new state is bad data at best. IMO, it would be a sloppy bandaid to the real problem.
What it seems we need is a way for the graphs to interpolate the date better in the case of binary values. Perhaps a parameter when putting the chart on the site map that can be used to tell the chart to assume binary values. Basically, assume a value of OFF at the precise moment before a value of ON is in the table. Thus avoiding sloped lines.
It would also need to work with 0 and 1 states as well as OFF and ON.
I have eventually also come to a conclusion that making data redundant is not the way to go.
By the way: it is not only about binary data, but about any discreet values with step big enough to make a visual difference in the chart.
In particular, my gas heating provides data of fan speed in RPM and it seems to rotate at some arbitrary speed steps only - hence I want it to be graphed the same way as the binary data, in sudden steps.
For each value in the table it will put a point on the chart and connect the points with a line. So unless you save intermediate values the chart is not going to give you the nice flat lines you are looking for. This is standard charting behavior (e.g. put values into Excel and chart them and you will get your āWrong dataā chart).
If I understand your request, what you are looking for is a change in this behavior so you will get the nice flat graph but without saving intermediate values. This does indeed make a lot of sense for data such as switches but does it make sense for other data types? For example, does it make sense for charting Temperature values, power consumption, etc?
Before demanding a change in behavior like this I think these other cases needs to be considered. Personally, I donāt think it always makes sense to chart these sorts of values using the scheme described above so perhaps a flag would be the way to go.
But until someone issues a change request (which will only be implemented in OH 2 anyway) it seems reasonable to me to drop your Switch and other data you want to be charted without being spiky into rrd4j and use an every minute (or faster) strategy. At least rrd4j doesnāt grow.
Finally, it may make sense to look into some third party charting capabilities. I believe InfluxDB has a bunch of good charting capabilities and Habmin has some good charting options as well, though Iāve not managed to get them to work for me.
I assume the flag might be ādiscreteā and ācontinuousā data to allow the user to choose appropriate charting behaviour, the latter being a default value.
It would be nice to have a further modification for ādiscreteā values:
Discrete/forwards: to keep chart line at the level of the last known value until the time of the new different value;
For any switch (on/off), stepped regulation (level 1 - 5 of gas heating power).
Discrete/backwards: to change the chart line level immediately to the next different value (i.e. at the timestamp of the preceeding value).
For the gas/water/electricity consumption metered by pulse sensors (magnetic reed, photoresistor) - i.e. basically showing the average consumption for the period of time before the pulse.
I am migrating my gathered data for about a 400 days period to database and plan to use openHAB to feed the database in the future. Despite the fact that openHAB is not yet very advanced with charting capabilities, I consider its open architecture as a promise for the future.
For now, I will try to store interval data to rrd4j and everyChange data to jdbc:mariadb in paralel.
Before my come&know openHAB I have been using simple bash scripts to produce static HTML pages for each calendar week with HighCharts, so I probably stick to that solution if rrd4j charts do not make me happy.
Also to not forget, just display the discrete values without drawing the lines. So you can interpret it by yourself and maybe have a better overview of how often values are stored, is the sensor working or not. I am working with different sensors and the gab can be small or big and therefore the change of the discrete number small or big.
Is there an addon which can handle that kind of stuff?
Key thing to note about my suggestion is having a parameter to choose the graph type. I agree this shouldnāt be a change that applies to ALL graphing types.
I donāt disagree that the chart is doing what normal charting would do. BUT, there is no easy way to get the intermediate data into the database in a reliable method. Simply adding functionality to the chart would save lots of intermediate data from being jammed into everybodyās database to make the graphs more readable.
From my perspective (Iām neither a developer nor a maintainer for OH so my opinion isnāt worth much) OH is designed for and does a good job of solving the problem of home automation, not data analysis. Presenting data in all sorts of charts and graphs with lots of options for how it makes and formats the chart to me still sounds like something where a third party tool would provide a far superior product and experience than we should ever expect from OH.
That having been said, Habmin2 has some nice charting options built in.
Also, now that I think about it, if you create a parallel Item for charting and a rule to update it you can get the behavior you want through a rule.
#Items:
Switch mySwitch
Switch persisted_mySwitch
#Rule:
rule "Persist mySwitch"
when
Item mySwitch received update
then
persisted_mySwitch.update(mySwitch.previousState.state.toString)
persisted_mySwitch.persist
persisted_mySwitch.update(mySwitch.state.toString)
persisted_mySwitch.persist
end
Make sure to exclude persisted_mySwitch from your .persist file so it doesnāt get persisted automatically. Make sure to include mySwitch in your .persist file so previousState works.
This will drop the previous value into the DB right before the new value as @Derka described above.
Unfortunately given the behavior of rule triggers with Groups I donāt think there will be a good way to avoid writing a separate rule for each Item you want to chart.
In your chart, use persisted_mySwtich instead of mySwitch.
I donāt disagreeā¦ but I donāt think we are talking data analysis. More just suggesting an extension to the charting.
An argument could be made that the current charting is somewhat inadequate for a good portion of items even in a very basic setting. My charts for when my heat was on or off are painful to look at. I donāt need to pump data into cacti or something so that I can drill into it for greater detail. I just want to glance and see if my basement heat is coming on enough during a cold snap to keep the pipes from freezing and I need to be able to do so from my phone.
Your solution is a decent workaround but adds extra database tables and entries as well as extra items and rules to build, maintain and hold in memory. I maintain the āproperā solution is to extend charts rather than find ways to modify data to feed to the chart. That being said, I may use it myself at least as a fill in until graphs are extended
Donāt get me wrong, Iām not arguing or finding fault with OH, Iām merely seeing a way that I think myself and others would enjoy an enhancement to OH and thatās what OH and these forums are about in my opinion.
But it works now and stores far less data than storing something every minute or something.
My personal approach to these sorts of things boil down to if is a real problem solve it. If it is a theoretical problem or if it is an aesthetic problem donāt solve it until it becomes a real problem. To me, having to store some extra values in a DB, particularly if you are using something like rrd4j, isnāt even a theoretical problem, it is one of aesthetics. It doesnāt look right to store redundant data.
If we are talking about something like a switch or any other discrete item and we store the value every minute it amounts to a few kilobytes of extra data over a year. And if you are using rrd4j, the DB self compresses so you never risk running out of space. But even if you are not using db4o or the like and you use the rule above a few extra KB or even MB a year isnāt something Iād be willing to spend time on. Even on a Raspberry Pi, I just donāt see that being a real problem. Particularly if you are using a DB that compresses its data like MySQL.
But if DB storage is really a problem or you have concerns about storing lots of extra tables and such you should be finely tuning your persistence anyway to store as little as needed.
For example, I use the following tiered approach:
Everything gets persisted to mapdb for restore on startup
Anything I want to chart or get historical values from gets stored in rrd4j. For rrd4j you will have to store values every minute no matter what for charting and DB queries anyway so the workaround above wouldnāt be needed.
Anything that I want to really study and have accurate historical data or I want to chart without storing lots of intermediate values which rrd4j would require gets stored in something like db4o or MySQL. Though this DB will continue to grow forever so you will need to plan on maintenance (i.e. deleting old values) at some point.
Using the scheme above, mySwitch would only be stored in mapdb, persisted_mySwitch would be stored in db4o or MySQL using the rule above or rrd4j using the every minute strategy.
And donāt get me wrong, Iām not arguing that the discussion shouldnāt be taking place or doesnāt belong here. Iām merely presenting a counter opinion. There are lots of problems to solve with OH and a lot of problems are simply not going to be fixed any more on OH 1. So we are in a period of transition where workarounds like the one I posted above will be very common until OH 2 is officially released and stabilized. So I tend to be a bit down on supporting proposals for changes to OH (right now) that do not really address OHās core mission. I tend to put tweaks to the charting servlet into that category.
Iād love to hear your experience with letsencrypt and why you chose nginx versus something a little less heavy weight like Apache. Iāve had the security of my deployment in the back of my mind but Iāve not really done much with it yet beyond auditing the logs periodically. I like to hear what others are doing.
Possibly due to the built in reverse proxy? I certainly hope it isnāt for its scaleability
rikoshak, your points are valid and I suppose waiting for OH2 makes sense. I have tweaked my rules similar to your suggestion in the meantime. Something if I had taken half a second to look at my rules I could have done months ago but sometimes it takes the kick in the pants.
Some of my āpassionā may be emotionally linked to my frustration with persistence in OH. Iām hoping part of the user friendly approach to OH 2, it will be better.
Iād like to revive this topic.
My suggestion is to add a new strategy like āeveryChangeExtendedā, that persists like everyUpdate, but overvrites the last record in the database (updates the date stamp of the last entry), if the value is the same.
P.S. I tried to find the definition of the persistence strategies, or at least to understand the github structure of openhab repository, but w/o successā¦ Can somebody advise?
Thus destroying the record of when the change to current state occurred.
Iām in agreement with the others, this is not a data problem. Everything you need to construct graphs as required is already recorded with everyChange. Itās purely a presentation issue.
Iām sure youāve seen this, but it seems clear enough - what was you issue?
Rich,
I came across your post since Grafana rendering is causing me a lot of trouble.
I am trying to chart a numerical value and would like to see the same result as Derka posted in the first post of this thread.
I see your suggestion is for a Switch item, how would that look if working with a Number item?
Itās always better to create a new thread than to reopen a thread that was started over four years ago.
Anyway, if you are using Grafana, just use the settings that come with Grafana. There is a fill attribute or something like that where you can tell it how to fill in the gaps between points. Choose ālast valueā and it will repeat the last entry as a level line until a new value appears. Then there is another attribute you can use to set the graph to use a stair step. This is all in the Graphana docs and it is outside the scope of OH really. The above was in scope because OP was asking about the OH built in charting which fas far fewer options compared to Graphana.
