What do people use for "long term trending and visualisation"?

General
1

hi all,
I had been contemplating a couple of times, how other users address the issue of "visualising long term trends.". I kind of have even difficulty to properly describe what I am looking for as trending, logging and archiving seem to have different meaning to different people.
My background is in PLC programming (Siemens to be precise) and one of the main functions would be to visualise values over a longer time (days, weeks, months and even years) in graphs with respective values shown as curves. This could be for the purpose to assess certain process conditions in context of other process values, controller optimisation etc.
To do this for my home automation system (e.g. trending of underfloor heating components, including room and outside temperature, along with flow / return flow temperatures and boiler on/off, as well as underfloor heating valves) I am "writing" these values to my homematic CCU (via node-red-contrib-ccu). In turn, homematic has an add-on package called CCU-Historian, which then allows a dynamic creation of charts/graphs of this nature:

image
image1200Ă—658 235 KB

So, values get stored in the ccu-historian "database" (really a text file of some sorts), which can be maintained (deleted, compressed, etc. ) over time.
But most importantly, these "survive" any kind of reboot and restart (of course with the exception of the short time, while the system is down). Once you have put together your "graph / curve selection", these can be "recalled" by means of that particular url (values and trend times are stored as a string in the url).

As I am not sure, how much longer I will operate the homematic "in parallel" to my node red RPi, I am always on the lookout for a "simple" replacement of the CCU Historian.

While there is the "ui_chart" function in the dashboard, which is nice for short term trends, I struggle to make any "easy to use" long term trends, which would also survive the above mentioned restart / reboot of the node red RPi.

I am aware that with a "good" logger, there are many moving parts (e.g. database / value maintenance, ui for curve selection / creation, etc.). As I cannot imaged, that I am the "only home enthusiast" with such a requirement, I wanted to find out, how other have done that in their installation!
Looking forward to your input!
THANKS
Robert

2

Grafana all the way. It is easy to setup and has great/flexible visualisations.

6 Likes
3

+1 for Grafana. With Influxdb for the data.

7 Likes
4

If you are not afraid to make your hands dirty with a bit of coding - take raw uPlot with your favoriite database and you can fine-tune everything. Next choice will be well know grafana route...

2 Likes
5

I keep data acquisition and storage separate from display and analysis.

The database is Mariadb.
I've tried Influxdb and mongodb but I use several views and calculated data within the database and found they were not as flexible as a proper SQL database, or else required different syntax.

For display I just use the default dashboard widgets.
Undoubtedly Grafana gives you a much more sophisticated view.
Presumably at some time I can feed SQL data into grafana and benefit from it.

6

calculated data within the database

What you mean with this statement ?

SQL database is used for relational general purpose data.

Mongdb is a database based on (json) documents and is schema-less also general purpose.

Influxdb is specifically build for timebased series and combines the 2, it is perfectly geared towards grafana (timebased), where sql is not, it will work, but not as performant as influx. This you will find out once you have a million datapoints.

7

Well here's a snippet of a query that does calculations in the database. Not recommending it, it's just one way I use Mariadb

SELECT
blah, blah,
ROUND(100 * (((h.offkwh - h.onkwh)1000 - m.minp)/ (m.maxp - m.minp))) as pestimate,
ROUND(100(time_to_sec(timediff(h.offtime, h.ontime)) - m.mint)/(m.maxt - m.mint)) as estimate,
case when date(p.ontime) = date(h.ontime) then 0 else 1 end as firstofday
from list of tables h, p, m

8

This is slightly off-topic. Do you have an example for real-time data display using uPlot?

The line chart from dashboard is too slow for real-time data display. We may have to get hands dirty :sweat_smile:

9

Of course it has been done :slight_smile:

1 Like
10

Can you describe what you are trying to do and in what way the chart is too slow?

11

Replaying to myself to just clarify - the charting is not only and is far from being best tool for long term process analysis. It may be easiest to set up but hardest to read and slowest in the path from data to solution.

Having computer in your hand which can make rocket to fly and land and get back from the moon, the analytical logic of simple stuff like heating/cooling/humidity in environment is just just a bit learning curve. Don't force yourself to be the computer - reading long term charts and making decisions - write logic to do it without any need of charting.

1 Like
12

There are many reasons for drawing charts other than for long term process analysis. For example they can help dramatically with the understanding of a process.

2 Likes
13

Absolutely. The charting is not pointless thing. Specially if the charted situation goes under discussion between many people to make some decision ... many more possibilities here ... but yeah.
I think you get my point. Let's keep the thread going in topic-

14

We have acceleration data coming in at the rate of more than 10kB/s wirelessly some times.

We are showing only 5 messages per second in the line chart right now. So there is distortion of the signal in the live view.

Of course, history data replay has no problem showing the real signal.

It would be ideal to have a fast responsive line chart. I will be happy if we can show more than 20 messages per second, which makes the live view very useful for some applications.

15

uPlot claims to be one of the fastest JavaScript library available

https://leeoniya.github.io/uPlot/demos/sine-stream.html

or

https://leeoniya.github.io/uPlot/demos/stream-data.html

1 Like
16

The chart looks very efficient at 60 FPS.

One missing piece in our current dashboard puzzle could be found :heart_eyes:

17

There is also a webgl based plotter, which is extremely fast

18

I use MRTG with RRD. It's a bit old school and does long term series like you want.

image
image1799Ă—393 220 KB

My MQTT to RRD script was written in Perl and runs as a systemd service which plots based on an well defined MQTT topic and payload. See GitHub - mrdvt92/mqtt-topic-grapher: MQTT Topic Grapher - Perl Script to convert MQTT data to RRD and MRTG

2 Likes
19

How about plotly.js?

20

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.