I hope you're all well in these crazy times. However, after a great deal of tinkering, I have failed to find an answer for myself and humbly ask for some help.
I'm a complete newbie when it comes to Node Red. While I'm vaguely proficient (albeit rusty) at Python and databases, my biggest weakness is that I have absolutely zero JS/JSON experience. I'm tinkering with my first Raspberry Pi based project, and it's a simple weather station.
I'd really appreciate a "walk-through" to get a feel on how to do things the "Node Red way" with one specific question in mind:
How do I "massage" my weather data from a simple MySQL select statement to a dashboard chart-friendly format. This is such a basic thing, but I struggle to find meaningful answers. Particularly when trying to get multiple database-driven lines on a chart. I'd really like to see the overall flow, then how each node is configured in series, with some indication about what to look for.
After days, I've failed to convert a MySQL query to a multi-line graph. All I want to do is to summarize old weather data to have the daily min, average, and max temperatures on a single graph.
What days of searching has taught me:
Changing the SQL statement involves something about message topics for each series, and then some seem to use a change node to somehow translate the multiple values into a series, but I have seen no working examples that go from start to finish.
I've tried to do things in almost every "wrong" way conceivable (according to Node Red, at least). Along the way, I was trying to enter my own non-standard time data and of course it failed). I managed to fix that, and got a couple of single-lined charts to work.
I managed to get a couple of gauges working after days of trawling through the web, but also found that any SQL statement that used functions like SUM(), MIN(), MAX(), or AVG() were passed to the chart in ways like "SUM(RAINFALL)" that moved the needle of the gauge to the right place, but didn't depict the value in the centre. It was fixed if I amended the SQL query to map the functions to gauge-friendly names like:
SELECT SUM(RAINFALL) AS TOTAL_RAIN FROM WEATHER...;
I can get one line on a graph, but all attempts to get MIN(), MAX(), & AVG() series have only rendered one line so far.
Having said that, please assume I know nothing, and need to start from scratch. I'm not even sure if I should create a separate SQL query for each series, or if I can extract each value from a multi-columned select statement and transmogrify it into a chart-friendly format further down the flow.
Any advice would be greatly appreciated.