This is my first post, sorry it's a bit long-winded ...
I have an outstation sending data to Node-Red via MQTT. The data is sent every 30s and is (temperature 1, temperature 2, humidity, pressure, voltage 1, voltage 2, voltage 3, voltage 4).
I want to chart this data in multiple charts from periods of 8 days to 30 minutes and I want to have this data re-load following server restarts so the incomming data needs to be stored on the server.
I have Node-Red, Mosquitto and SQLITE running on a PI.
My initial approach was to send the incoming data to a chart and to a file.
On start-up, the data from the file is loaded into the chart and then incoming data further populates the chart.
This approach has the merit of simplicity and works well for a few days.
I have a file per chart data stream, but these files obviously just grow and grow. As the files get bigger, Node-Red slows down and further development with this approach seems inelegant.
I decided to try to send the data to a SQLITE database and chart it from there. This is a work-in-progress and I've got this partially working with an inject node manually to create a chart.
I am writing the incoming data to a database table together with unix time and also human readable date and time.
(a) Manual inject of the query to select pressure data results in a graph with missing points as can be seen from the straight line on the chart (following the highlighted data point). This is despite the underlying data being present in the database and this result is reproducable.
My select node is simply
"SELECT time as "x", PRESSURE as "y" FROM sensors WHERE currenttime >= date('now','-60 minutes')"
and the change node is
Now, I know I should not have 64000 in there, but I wanted to ensure I get all the records from the query, and if I make that a low number some could be missed. I would really like to set all my queries at 8 days and then where I want less time displayed, define this on the chart node. [I envisage later on perhaps having buttons to allow me to choose the duration displayed].
What's the elegant approach to this and how do I ensure that all data points are written to the chart?
(b) The chart doesn't persist once created. I think what's happening is that the insert function generates an output from the database node which clears the chart. I want multiple persistent charts with different timescales and am not sure what I'm missing, but it must be obvious!
(c) I want to generate charts that update every time there is new data.
I suspect that it's really inefficient to run a query every 30s and fully regenerate all graphs plus visually this is not attractive to watch.
I thought that an approach could be to populate the chart historic content at start-up and then simply feed real-time data to the chart but this doesn't seem to work the way I've tried in the flow.
Again, I'm looking for how best to do this; I don't really like the idea of constantly re-generating charts and would prefer just to send new data to the chart in the same way that this works using local files.
Finally, I remain uncertain about the merits of JSONata vs a function node approach for setting up the chart data.
Any pointers would be much appreciated!