I am trying to get a graph from my roomtemperature.
I found some examples and it took me a while to get them working but I managed.
I have a database with an ID, sensornumber, value and a sql generated timestamp.
Whatever I try, I don't see anything on my graph.
This is my flow:
[{"id":"57ae6962.0687b8","type":"tab","label":"Grafiek kamertemp","disabled":false,"info":""},{"id":"4bfec7e1.b008c","type":"template","z":"57ae6962.0687b8","name":"format query wk","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"SELECT mw_data \nFROM meetwaarden \nWHERE mw_se_id = 1\n AND mw_timestamp > \"{{payload}}\"","output":"str","x":660,"y":80,"wires":[["a7458f1c.229c78","78527ea9.9c2d68"]]},{"id":"e555e6b6.6a363","type":"debug","z":"57ae6962.0687b8","name":"1","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":630,"y":40,"wires":[]},{"id":"a7458f1c.229c78","type":"debug","z":"57ae6962.0687b8","name":"2","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":870,"y":80,"wires":[]},{"id":"9abd05c0.629ab","type":"function","z":"57ae6962.0687b8","name":"convert","func":"if ( !msg.timestamp ) msg.timestamp = Math.round(+new Date());\n\nvar dt = new Date(msg.payload);\nvar Y = dt.getFullYear().toString();\nvar M = (dt.getMonth() + 1).toString();\nif((dt.getMonth() + 1)<=9){M='0'+M}\nvar D = dt.getDate().toString();\nif(dt.getDate()<=9){D='0'+D}\nvar H = dt.getHours().toString();\nif(dt.getHours()<=9){H='0'+H}\nvar m = dt.getMinutes().toString();\nif(dt.getMinutes()<=9){m='0'+m}\n//var S = dt.getMilliseconds().toString();\nmsg.payload = Y+'-'+M+'-'+D+' '+H+':'+m+':'+'00';\n\nreturn msg;","outputs":1,"noerr":0,"x":440,"y":80,"wires":[["e555e6b6.6a363","4bfec7e1.b008c"]]},{"id":"6ef19a5d.2508d4","type":"inject","z":"57ae6962.0687b8","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":100,"y":80,"wires":[["84775b33.2d0af"]]},{"id":"77b08af2.e488d4","type":"ui_chart","z":"57ae6962.0687b8","name":"Kamer temperatuur","group":"d3e2514d.48f4c","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":"","removeOlderUnit":"3600","cutout":0,"useOneColor":false,"useUTC":false,"colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"useOldStyle":false,"outputs":1,"x":610,"y":180,"wires":[["4373954c.03ee0c"]]},{"id":"8525cd85.66fa48","type":"change","z":"57ae6962.0687b8","name":"Format data WK","rules":[{"t":"set","p":"payload","pt":"msg","to":"(\t $series := [\t { \"field\": \"mw_data\", \"label\": \"Kamertemp\" }\t ];\t $xaxis := \"timestamp\";\t [\t {\t \"series\": $series.label,\t \"data\": $series.[\t (\t $yaxis := $.field;\t $$.payload.{\t \"x\": $lookup($, $xaxis),\t \"y\": $lookup($, $yaxis)\t }\t )\t ]\t }\t ]\t)","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":340,"y":180,"wires":[["ab2f4d1e.a881f8","77b08af2.e488d4"]]},{"id":"78527ea9.9c2d68","type":"mysql","z":"57ae6962.0687b8","mydb":"98a864b9.d0edb","name":"MYSQL","x":120,"y":180,"wires":[["2b2c2b71.f0d6cc","8525cd85.66fa48"]]},{"id":"2b2c2b71.f0d6cc","type":"debug","z":"57ae6962.0687b8","name":"3","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":310,"y":240,"wires":[]},{"id":"ab2f4d1e.a881f8","type":"debug","z":"57ae6962.0687b8","name":"4","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":530,"y":240,"wires":[]},{"id":"84775b33.2d0af","type":"function","z":"57ae6962.0687b8","name":"Criteria","func":"var timeE = msg.payload;\n//Restrict the query to pull the last 24hrs\n//of data instead of the whole db\nmsg.payload = (timeE - (1000*60*5));\n node.status({text:msg.payload});\nreturn msg;","outputs":1,"noerr":0,"x":260,"y":80,"wires":[["9abd05c0.629ab"]]},{"id":"4373954c.03ee0c","type":"debug","z":"57ae6962.0687b8","name":"5","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":810,"y":240,"wires":[]},{"id":"d3e2514d.48f4c","type":"ui_group","z":"","name":"Grafieken","tab":"e62eb9b.f9d3cc8","order":2,"disp":true,"width":"6","collapse":false},{"id":"98a864b9.d0edb","type":"MySQLdatabase","z":"","name":"","host":"127.0.0.1","port":"3306","db":"Huismanager","tz":""},{"id":"e62eb9b.f9d3cc8","type":"ui_tab","z":"","name":"Grafieken","icon":"dashboard","order":2,"disabled":false,"hidden":false}]
this is what is displayed by debug3:
5-4-2020 13:36:24node: 3
SELECT mw_data FROM meetwaarden WHERE mw_se_id = 1 AND mw_timestamp > "2020-04-05 13:31:00" : msg.payload : array[6]
array[6]
0: object
mw_data: "21.84"
1: object
mw_data: "21.85"
2: object
mw_data: "21.84"
3: object
mw_data: "21.85"
4: object
mw_data: "21.87"
5: object
mw_data: "21.92"
I tried to modify the javascript in the "Format data WK" but I am sure I do something wrong (otherwise it would have worked)
Can anybody give me some hint how to get this working?
-Warner-
And time enough through covid-19....
(yes, 13 digits)
