Hello, I've been using Node-red for a awhile but would like some suggestions on storing data and displaying a time series chart.
There is a sensor sending data 8 times a second, as well as a target setpoint, an average per minute from an array, and a maximum and minimum value. There is also an image that is sent to the browser but not stored in the database.
From what I understand, the best option may be to send the data to an SQLite DB, then send the data to chart, possibly dashboard 2.0.
Thank you in advance for any suggestions or direction.
The possible issue with using a SQLite DB might be the amount of accumulated data. If you are wanting historic data, you would be better off using a timeseries db such as InfluxDB. If you only need current data, you probably don't need a db at all, just a suitable context/flow/global variable.
As for displaying in the browser, while D2 is an option, you could also use UIBUILDER if you want more flexibility or don't want to use VueJS based displays.
I probably would use a database for this, because if something is important enough to need monitoring 8 times a second, you might value the historical data when it hits a snag.
Also it takes a (bit of) effort to show live data on a chart without losing it on deploy/restart.
That said, eight INSERT queries per second might overwhelm the DBMS, I'd probably accumulate 10 seconds worth and insert them as a batch.
Eight data points per second is overload for a chart too, almost 30000 per hour to show on a screen maybe 2000 pixels wide.
Do you need to detect very short duration spikes?
Could you get away with storing and charting an average value per minute?
InfluxDB and Grafana might be ideal for you; I found them hard to master.
I prefer an SQL database: SQLite or MariaDB
While InfluxDB is a little harder to start with if you are used to SQL, there is guidance on this forum and elsewhere for getting started and once set up, it rarely if ever needs touching.
For anything where you may wish to do time-based calculations for display or, as in the examples on this forum, consolidating the detailed data to hourly for longer term trend analysis, it is hard to beat a timeseries db since it will do it all for you automatically with no effort.
Graphana is nice in that it lets you create and manage charts that are far more complex than you could easily do yourself or via Dashboard. And, specifically when using InfluxDB, it has a point-and-click interface for building queries.
If using Dashboard, make sure you limit the amount of data in your charts as the underlying library isn't terribly efficient. If using UIBUILDER, you can choose your own charting library to do any kind of display.
