I have a Mysql with RealTime as primary key and when I send the query
SELECT AcX, RealTime from certain_table WHERE Realtime >’2019-09-11 22:02:00.00‘ LIMIT 200;
I use the Change Node to modify the payload to feed into the Chart
[
{
"series": ["AcX"],
"labels": ["AcX_label"],
"data": [
[
payload.{
"x": RealTime,
"y": AcX
}
]
]
}
]
And I got a nice chart with x-axis in milliseconds.
First question, I wonder if I feed 2000 data to the chart with 200 points setting, what will happen? Will it scale it for me? I tried to expand the chart to 2000 point but the response is slow.
My another idea is get the average data per minute and plot the chart, but I am not good at manipulate the javascript object, can I do it in the Change Node? Or can the chart node do anything about this function? Thx.
This is where databases really shine -- retaining historical data, yet allowing queries to summarize and report trends. It's best if you don't try to pull all the raw data into your flow (or chart!) and then aggregate it into time slots.
Instead, you can use a SQL query to group your data into 1 minute time slots -- the actual syntax may change depending on which database you have, but something like this:
SELECT AVG(AcX) as avg_acx,
FLOOR(UNIX_TIME(RealTime) / 60) * 60 AS time_slot
FROM certain_table WHERE Realtime >’2019-09-11 22:02:00.00‘
GROUP BY time_slot;
BTW, if you have a choice of which database to use, I would recommend InfluxDB, which makes summaries by groups of time very trivial -- although its syntax is not quite the same as SQL.
Thx. It is working except the starting time on the chart is not the same as in the millisecond chart...
The millisecond chart range is 09-04 23:56:25.367 - 09-04 23:56:26.380
But the average chart range is 10-29 08:30:00 - 08:46:00
Note: RealTime is the primary key
Is it the ORDER problem?Thx
millisecond query
SELECT * FROM certain_table where RealTime > '2019-09-00 01:01:01' LIMIT 200;
second query
SELECT AVG(AcX) as avg_acx, FLOOR((RealTime) / 60) * 60 AS time_slot FROM certain_table where RealTime > '2019-09-00 01:01:01' GROUP BY time_slot LIMIT 200;
If I use this query, I only get one object... Sorry, I am new to SQL... Thx
SELECT AVG(AcX) as avg_acx, FLOOR((RealTime) / 60) * 60 AS time_slot FROM lift_database.liftcar_table where RealTime > '2019-09-00 01:01:01' ORDER BY RealTime ASC LIMIT 200; : msg.payload : array[1]
Bakman2 is correct -- if you want to use the AVG() function you need the GROUP BY ...
Your difference in the times on the chart tics is because it expects JS millis (ms since the epoch), where the sql UNIX_TIME() function converts it to Epoch seconds (sec since the epoch). You can do that sec -> millis conversion right in your select statement, just by multiplying by 1000:
Incidentally, using InfluxDB with Grafana is much more efficient for this kind of thing since InfluxDB has simple commands to aggregate data by time and Grafana can plot things much more intelligently.
It seems like the new node-red version is more stable. The dashboard seldom crash. I can feed data to mySQL at 1Hz smoothly. And I can get 2000 rows from mySQL without any problem. :> Thx for all people who helped. Cheers. And yes, I will try InfluxDB later. Thx
