Help needed to create graph

hi girls and guys

i am busy the whole day to generate a graph from my database but not able to fix this....
Currently my database has 2 entries ( just for test purpose )
In the future there will be more entrys.
Output from my sqlite3 command is:

sqlite3 test.db "select timestamp,value from temperatuur order by timestamp;"
1605720348906|20
1605720358906|210

I know the code is not correct and the "setup payload" has to change.
I was busy whole day to figure this out...but without any success.

Just want to make a graph of this 2 entrys.
I hope some one can help me out here.

many thanks in advance
Menno

[{"id":"62ac5679.49b0a8","type":"sqlite","z":"a6cd331c.cc4ef","mydb":"77ec0636.02da08","sqlquery":"msg.topic","sql":"","name":"testdb","x":250,"y":80,"wires":[["a93ae5b7.ae0c68"]]},{"id":"8d060df4.310e3","type":"inject","z":"a6cd331c.cc4ef","name":"viewit","topic":"select timestamp,value from Temperatuur order by timestamp","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":90,"y":80,"wires":[["62ac5679.49b0a8"]]},{"id":"e315da4b.e516e8","type":"debug","z":"a6cd331c.cc4ef","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":970,"y":80,"wires":[]},{"id":"a93ae5b7.ae0c68","type":"change","z":"a6cd331c.cc4ef","name":"Data Array","rules":[{"t":"set","p":"data","pt":"msg","to":"$.values.{\"timestamp\":timestamp, \"value\":value}","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":410,"y":80,"wires":[["e315da4b.e516e8"]]},{"id":"9b65fad0.5742f8","type":"function","z":"a6cd331c.cc4ef","name":"Setup payload","func":"series = [];\npayload = [];\n\n\nseries.push(\"timestamp\");\npayload.push(\"value\");\n\n\nmsg.payload = [{\"series\":series,\"value\":data}];\n\nreturn msg;","outputs":1,"noerr":0,"x":600,"y":120,"wires":[[]]},{"id":"4461c0f9.a9dc4","type":"ui_chart","z":"a6cd331c.cc4ef","name":"","group":"4240491b.780218","order":3,"width":0,"height":0,"label":"payload","chartType":"line","legend":"false","xformat":"HH:mm","interpolate":"linear","nodata":"","dot":true,"ymin":"","ymax":"","removeOlder":"1","removeOlderPoints":"","removeOlderUnit":"3600","cutout":0,"useOneColor":false,"useUTC":false,"colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"outputs":1,"x":800,"y":120,"wires":[[]]},{"id":"77ec0636.02da08","type":"sqlitedb","z":"","db":"/tmp/test.db","mode":"RWC"},{"id":"4240491b.780218","type":"ui_group","z":"","name":"Coffee","tab":"8161fe00.00e72","order":5,"disp":true,"width":4,"collapse":false},{"id":"8161fe00.00e72","type":"ui_tab","z":"","name":"Energy","icon":"fa-1x fa-bolt","order":3,"disabled":false,"hidden":false}]

image

Try this, it should convert the timestamp to x and value to y and insert result into msg.payload[0].data[0] .

[{"id":"199c62c.8c2ad1d","type":"change","z":"c74669a0.6a34f8","name":"Datset chart payload","rules":[{"t":"set","p":"hold[0].data[0]","pt":"msg","to":"payload.{\"x\":timestamp, \"y\":value}","tot":"jsonata"},{"t":"set","p":"hold[0].series","pt":"msg","to":" [\"A\"]","tot":"json"},{"t":"move","p":"hold","pt":"msg","to":"payload","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":380,"y":2580,"wires":[["f0d03ec0.e22b08","e315da4b.e516e8"]]},{"id":"8d060df4.310e3","type":"inject","z":"c74669a0.6a34f8","name":"result from db","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[{\"timestamp\":1605720348906,\"value\":20},{\"timestamp\":1605720358906,\"value\":210}]","payloadType":"json","x":150,"y":2580,"wires":[["199c62c.8c2ad1d"]]},{"id":"f0d03ec0.e22b08","type":"ui_chart","z":"c74669a0.6a34f8","name":"","group":"8b5cde76.edd58","order":4,"width":0,"height":0,"label":"chart","chartType":"line","legend":"false","xformat":"HH:mm:ss","interpolate":"linear","nodata":"","dot":false,"ymin":"","ymax":"","removeOlder":1,"removeOlderPoints":"100","removeOlderUnit":"3600","cutout":0,"useOneColor":false,"useUTC":false,"colors":["#d73920","#ffcb13","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"outputs":1,"useDifferentColor":false,"x":590,"y":2580,"wires":[[]]},{"id":"e315da4b.e516e8","type":"debug","z":"c74669a0.6a34f8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":610,"y":2480,"wires":[]},{"id":"8b5cde76.edd58","type":"ui_group","name":"","tab":"8f03e639.85956","order":1,"disp":true,"width":"6","collapse":false},{"id":"8f03e639.85956","type":"ui_tab","name":"Home","icon":"dashboard","disabled":false,"hidden":false}]

Looks OK .. i will give it a try

THX MAN !!!!

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