Ok, will do that tomorrow, it's 23:00 so bedtime...
Thanks so far...
I think with $parseInteger....
You might want to use $number to cast them as number with decimals included.
Chart requires in the data field an array of series, where the series is array of objects.
You are missing one array in data creation.
I now created a test environment where I don't have the mysql:
[{"id":"5afbcee9.2a3598","type":"tab","label":"probeersel","disabled":false,"info":""},{"id":"82c66cd2.36a7d","type":"inject","z":"5afbcee9.2a3598","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":180,"y":340,"wires":[["4fb76dce.4f250c"]]},{"id":"be619be6.db8078","type":"change","z":"5afbcee9.2a3598","name":"Format data WK","rules":[{"t":"set","p":"payload","pt":"msg","to":"(\t $series := [\t { \"field\": \"mw_data\", \"label\": \"Kamertemp\" }\t ];\t $xaxis := \"UNIX_TIMESTAMP(mw_timestamp)\";\t [\t {\t \"series\": [$series.label],\t \"labels\": [$series.label],\t \"data\": $series.[\t (\t $yaxis := $.field;\t $$.payload.{\t \"x\": $lookup($, $xaxis)*1000,\t \"y\": $number($lookup($, $yaxis))\t }\t )\t ]\t }\t ]\t)","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":560,"y":340,"wires":[["8cced96e.dc1fa","41d96663.83c158"]]},{"id":"4fb76dce.4f250c","type":"function","z":"5afbcee9.2a3598","name":"data inject","func":"msg.payload=\n[{\"UNIX_TIMESTAMP(mw_timestamp)\":1587194761,\"mw_data\":\"19.28\"},\n{\"UNIX_TIMESTAMP(mw_timestamp)\":1587194821,\"mw_data\":\"19.29\"},\n{\"UNIX_TIMESTAMP(mw_timestamp)\":1587194881,\"mw_data\":\"19.34\"},\n{\"UNIX_TIMESTAMP(mw_timestamp)\":1587194941,\"mw_data\":\"19.18\"},\n{\"UNIX_TIMESTAMP(mw_timestamp)\":1587195001,\"mw_data\":\"19.34\"},\n{\"UNIX_TIMESTAMP(mw_timestamp)\":1587195061,\"mw_data\":\"19.08\"},\n{\"UNIX_TIMESTAMP(mw_timestamp)\":1587195121,\"mw_data\":\"19.1\"},\n{\"UNIX_TIMESTAMP(mw_timestamp)\":1587195181,\"mw_data\":\"19.32\"},\n{\"UNIX_TIMESTAMP(mw_timestamp)\":1587195241,\"mw_data\":\"19.49\"},\n{\"UNIX_TIMESTAMP(mw_timestamp)\":1587195301,\"mw_data\":\"19.53\"},\n{\"UNIX_TIMESTAMP(mw_timestamp)\":1587195361,\"mw_data\":\"19.56\"},\n{\"UNIX_TIMESTAMP(mw_timestamp)\":1587195421,\"mw_data\":\"19.54\"},\n{\"UNIX_TIMESTAMP(mw_timestamp)\":1587195481,\"mw_data\":\"19.58\"},\n{\"UNIX_TIMESTAMP(mw_timestamp)\":1587195541,\"mw_data\":\"19.62\"},\n{\"UNIX_TIMESTAMP(mw_timestamp)\":1587195601,\"mw_data\":\"19.67\"},\n{\"UNIX_TIMESTAMP(mw_timestamp)\":1587195661,\"mw_data\":\"19.67\"},\n{\"UNIX_TIMESTAMP(mw_timestamp)\":1587195721,\"mw_data\":\"19.67\"},\n{\"UNIX_TIMESTAMP(mw_timestamp)\":1587195781,\"mw_data\":\"19.67\"},\n{\"UNIX_TIMESTAMP(mw_timestamp)\":1587195841,\"mw_data\":\"19.64\"},\n{\"UNIX_TIMESTAMP(mw_timestamp)\":1587195901,\"mw_data\":\"19.63\"},\n{\"UNIX_TIMESTAMP(mw_timestamp)\":1587195961,\"mw_data\":\"19.66\"},\n{\"UNIX_TIMESTAMP(mw_timestamp)\":1587196021,\"mw_data\":\"19.63\"},\n{\"UNIX_TIMESTAMP(mw_timestamp)\":1587196081,\"mw_data\":\"19.67\"},\n{\"UNIX_TIMESTAMP(mw_timestamp)\":1587196141,\"mw_data\":\"19.67\"},\n{\"UNIX_TIMESTAMP(mw_timestamp)\":1587196201,\"mw_data\":\"19.67\"},\n{\"UNIX_TIMESTAMP(mw_timestamp)\":1587196261,\"mw_data\":\"19.65\"},\n{\"UNIX_TIMESTAMP(mw_timestamp)\":1587196321,\"mw_data\":\"19.51\"},\n{\"UNIX_TIMESTAMP(mw_timestamp)\":1587196381,\"mw_data\":\"19.5\"},\n{\"UNIX_TIMESTAMP(mw_timestamp)\":1587196441,\"mw_data\":\"19.14\"},\n{\"UNIX_TIMESTAMP(mw_timestamp)\":1587196501,\"mw_data\":\"19.29\"},\n{\"UNIX_TIMESTAMP(mw_timestamp)\":1587196561,\"mw_data\":\"19.44\"},\n{\"UNIX_TIMESTAMP(mw_timestamp)\":1587196621,\"mw_data\":\"19.48\"},\n{\"UNIX_TIMESTAMP(mw_timestamp)\":1587196681,\"mw_data\":\"19.5\"},\n{\"UNIX_TIMESTAMP(mw_timestamp)\":1587196741,\"mw_data\":\"19.49\"},\n{\"UNIX_TIMESTAMP(mw_timestamp)\":1587196801,\"mw_data\":\"19.5\"},\n{\"UNIX_TIMESTAMP(mw_timestamp)\":1587196861,\"mw_data\":\"19.52\"},\n{\"UNIX_TIMESTAMP(mw_timestamp)\":1587196921,\"mw_data\":\"19.52\"},\n{\"UNIX_TIMESTAMP(mw_timestamp)\":1587196981,\"mw_data\":\"19.5\"},\n{\"UNIX_TIMESTAMP(mw_timestamp)\":1587197041,\"mw_data\":\"19.48\"},\n{\"UNIX_TIMESTAMP(mw_timestamp)\":1587197101,\"mw_data\":\"19.5\"},\n{\"UNIX_TIMESTAMP(mw_timestamp)\":1587197161,\"mw_data\":\"19.51\"},\n{\"UNIX_TIMESTAMP(mw_timestamp)\":1587197221,\"mw_data\":\"19.54\"},\n{\"UNIX_TIMESTAMP(mw_timestamp)\":1587197281,\"mw_data\":\"19.54\"},\n{\"UNIX_TIMESTAMP(mw_timestamp)\":1587197341,\"mw_data\":\"19.54\"},\n{\"UNIX_TIMESTAMP(mw_timestamp)\":1587197401,\"mw_data\":\"19.54\"},\n{\"UNIX_TIMESTAMP(mw_timestamp)\":1587197461,\"mw_data\":\"19.54\"},\n{\"UNIX_TIMESTAMP(mw_timestamp)\":1587197521,\"mw_data\":\"19.55\"},\n{\"UNIX_TIMESTAMP(mw_timestamp)\":1587197581,\"mw_data\":\"19.6\"},\n{\"UNIX_TIMESTAMP(mw_timestamp)\":1587197641,\"mw_data\":\"19.6\"},\n{\"UNIX_TIMESTAMP(mw_timestamp)\":1587197701,\"mw_data\":\"19.63\"},\n{\"UNIX_TIMESTAMP(mw_timestamp)\":1587197761,\"mw_data\":\"19.67\"},\n{\"UNIX_TIMESTAMP(mw_timestamp)\":1587197821,\"mw_data\":\"19.68\"},\n{\"UNIX_TIMESTAMP(mw_timestamp)\":1587197881,\"mw_data\":\"19.7\"},\n{\"UNIX_TIMESTAMP(mw_timestamp)\":1587197941,\"mw_data\":\"19.72\"},\n{\"UNIX_TIMESTAMP(mw_timestamp)\":1587198001,\"mw_data\":\"19.73\"},\n{\"UNIX_TIMESTAMP(mw_timestamp)\":1587198061,\"mw_data\":\"19.73\"},\n{\"UNIX_TIMESTAMP(mw_timestamp)\":1587198121,\"mw_data\":\"19.75\"},\n{\"UNIX_TIMESTAMP(mw_timestamp)\":1587198181,\"mw_data\":\"19.66\"},\n{\"UNIX_TIMESTAMP(mw_timestamp)\":1587198241,\"mw_data\":\"19.68\"},\n{\"UNIX_TIMESTAMP(mw_timestamp)\":1587198301,\"mw_data\":\"19.75\"},\n{\"UNIX_TIMESTAMP(mw_timestamp)\":1587198361,\"mw_data\":\"19.77\"},\n{\"UNIX_TIMESTAMP(mw_timestamp)\":1587198421,\"mw_data\":\"19.77\"},\n{\"UNIX_TIMESTAMP(mw_timestamp)\":1587198481,\"mw_data\":\"19.79\"},\n{\"UNIX_TIMESTAMP(mw_timestamp)\":1587198541,\"mw_data\":\"19.81\"},\n{\"UNIX_TIMESTAMP(mw_timestamp)\":1587198601,\"mw_data\":\"19.84\"},\n{\"UNIX_TIMESTAMP(mw_timestamp)\":1587198661,\"mw_data\":\"19.88\"}]\nreturn msg;","outputs":1,"noerr":0,"x":360,"y":340,"wires":[["7b1afd41.b1b604","be619be6.db8078"]]},{"id":"7b1afd41.b1b604","type":"debug","z":"5afbcee9.2a3598","name":"1","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":530,"y":400,"wires":[]},{"id":"41d96663.83c158","type":"ui_chart","z":"5afbcee9.2a3598","name":"Kamer temperatuur","group":"1b714cea.e5e0fb","order":1,"width":0,"height":0,"label":"Kamer temperatuur","chartType":"line","legend":"false","xformat":"HH:mm","interpolate":"linear","nodata":"","dot":false,"ymin":"","ymax":"","removeOlder":"24","removeOlderPoints":"100","removeOlderUnit":"604800","cutout":0,"useOneColor":false,"useUTC":false,"colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"useOldStyle":false,"outputs":1,"x":790,"y":340,"wires":[["dd4d66f.1ebcc98"]]},{"id":"8cced96e.dc1fa","type":"debug","z":"5afbcee9.2a3598","name":"4","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":750,"y":400,"wires":[]},{"id":"dd4d66f.1ebcc98","type":"debug","z":"5afbcee9.2a3598","name":"5","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":970,"y":340,"wires":[]},{"id":"1b714cea.e5e0fb","type":"ui_group","z":"","name":"Kamer","tab":"c1485f14.e202c","order":1,"disp":true,"width":"6","collapse":false},{"id":"c1485f14.e202c","type":"ui_tab","z":"","name":"Huismanager","icon":"dashboard","order":1,"disabled":false,"hidden":false}]
Still no graph..
I managed to create a graph which gives the values of the last 24 hours, but that is not done through the database, I feed every minute the value into the graph. That works ok. But the the creation of a graph from database output remains a mystery even with all the examples on the net, even on node-red itself.
Put a debug node showing what it going to the chart to draw it. Does it match the documented layout? If not and you don't know how to make it do that then ask about that question, if you think it does match but doesn't work then post the debug output here.
This is going into the chart.
[{"series":["Kamertemp"],"labels":["Kamertemp"],"data":[{"x":1587194761000,"y":19.28},{"x":1587194821000,"y":19.29},{"x":1587194881000,"y":19.34},{"x":1587194941000,"y":19.18},{"x":1587195001000,"y":19.34},{"x":1587195061000,"y":19.08},{"x":1587195121000,"y":19.1},{"x":1587195181000,"y":19.32},{"x":1587195241000,"y":19.49},{"x":1587195301000,"y":19.53},{"x":1587195361000,"y":19.56},{"x":1587195421000,"y":19.54},{"x":1587195481000,"y":19.58},{"x":1587195541000,"y":19.62},{"x":1587195601000,"y":19.67},{"x":1587195661000,"y":19.67},{"x":1587195721000,"y":19.67},{"x":1587195781000,"y":19.67},{"x":1587195841000,"y":19.64},{"x":1587195901000,"y":19.63},{"x":1587195961000,"y":19.66},{"x":1587196021000,"y":19.63},{"x":1587196081000,"y":19.67},{"x":1587196141000,"y":19.67},{"x":1587196201000,"y":19.67},{"x":1587196261000,"y":19.65},{"x":1587196321000,"y":19.51},{"x":1587196381000,"y":19.5},{"x":1587196441000,"y":19.14},{"x":1587196501000,"y":19.29},{"x":1587196561000,"y":19.44},{"x":1587196621000,"y":19.48},{"x":1587196681000,"y":19.5},{"x":1587196741000,"y":19.49},{"x":1587196801000,"y":19.5},{"x":1587196861000,"y":19.52},{"x":1587196921000,"y":19.52},{"x":1587196981000,"y":19.5},{"x":1587197041000,"y":19.48},{"x":1587197101000,"y":19.5},{"x":1587197161000,"y":19.51},{"x":1587197221000,"y":19.54},{"x":1587197281000,"y":19.54},{"x":1587197341000,"y":19.54},{"x":1587197401000,"y":19.54},{"x":1587197461000,"y":19.54},{"x":1587197521000,"y":19.55},{"x":1587197581000,"y":19.6},{"x":1587197641000,"y":19.6},{"x":1587197701000,"y":19.63},{"x":1587197761000,"y":19.67},{"x":1587197821000,"y":19.68},{"x":1587197881000,"y":19.7},{"x":1587197941000,"y":19.72},{"x":1587198001000,"y":19.73},{"x":1587198061000,"y":19.73},{"x":1587198121000,"y":19.75},{"x":1587198181000,"y":19.66},{"x":1587198241000,"y":19.68},{"x":1587198301000,"y":19.75},{"x":1587198361000,"y":19.77},{"x":1587198421000,"y":19.77},{"x":1587198481000,"y":19.79},{"x":1587198541000,"y":19.81},{"x":1587198601000,"y":19.84},{"x":1587198661000,"y":19.88}]}]
I thought this should be correct.
I tried the example from https://flows.nodered.org/flow/13c55d1aa11e864609e24fa534a1fa26 but that has two lines and I have only one. If I modify that so it has only one line it doesn't work either....
The problem is I cannot find an example with a single line, only multiple. And the single line ones don't have a database as input, just realtime data.
In the debug node does it show as a String type or an Array type?
It took me a little time to realise, but you have a level of array nesting missing for the data, it needs to be
[
{
"series": [
"Kamertemp"
],
"labels": [
"Kamertemp"
],
"data": [
[
{
"x": 1587194761000,
"y": 19.28
},
{
"x": 1587194821000,
"y": 19.29
},
{
"x": 1587194881000,
"y": 19.34
},
{
"x": 1587194941000,
"y": 19.18
},
{
"x": 1587195001000,
"y": 19.34
}
]
]
}
]
Note that the data object has to be an array of arrays (one for each series).
Gosh! It works! Thanks!
My JSONata is now:
(
$series := [
{ "field": "mw_data", "label": "Kamertemp" }
];
$xaxis := "UNIX_TIMESTAMP(mw_timestamp)";
[
{
"series": [$series.label],
"labels": [$series.label],
"data": $series.[[
(
$yaxis := $.field;
$$.payload.{
"x": $lookup($, $xaxis)*1000,
"y": $number($lookup($, $yaxis))
}
)
]]
}
]
)
1 Like
This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.
