MYSQL Charts for Temperatur and Humidity

Hello people,

i am looking to create a chart for temperatur and humidity.
I am able to use the data and store it in sql.
But i dont know how to put it in a chart.
I dont knwo how to use the time and date correct
And i would like to show only 1 past day.
Can someone help me please?

My output bevore Chart looks like this

1.6.2021, 14:59:17node: 9a24e6f3.012a28
SELECT data,topic,timestamp FROM Temperatur : msg.payload : array[1]
[{"series":"","data":[[7,7,7,7,7,7,7.1,7.1,7.1,7.1,7.1,7.1,7.1,7.1,7.2,7.2,7.2,7.2,7.2,7.3,5.4,5.4,5.3,5.2,5.1,5,4.9,4.8,4.7,4.6,4.5,4.4,4.3,4.2,4.1,4,3.9,3.8,3.7,3.6,3.5,3.4,3.3,3.2,3.1,3,2.9,2.8,2.7,2.6,2.5,2.4,2.3,2.2,2.1,2,1.9,1.8,1.7,1.6,1.5,1.4,1.3,1.2,1.1,1,0.9,0.8,0.7,0.6,0.5,0.4,0.3,0.2,0.1,0,-0.1,-0.2,-0.3,-0.4,-0.5,-0.6,-0.3,-0.4,-0.5,-.............................]],"labels":[["2021-03-23T12:40:12.000Z","2021-03-23T12:40:16.000Z","2021-03-23T12:41:12.000Z","2021-03-23T12:42:12.000Z","2021-03-23T12:43:12.000Z","2021-03-23T12:44:12.000Z","2021-03-23T12:45:12.000Z","2021-03-23T12:46:12.000Z","2021-03-23T12:47:04.000Z","2021-03-23T12:47:12.000Z","2021-03-23T12:48:12.000Z","2021-03-23T12:49:12.000Z","2021-03-23T12:50:11.000Z","2021-03-23T12:53:54.000Z","2021-03-23T12:55:11.000Z","2021-03-23T13:02:20.000Z","2021-03-23T13:02:29.000Z","2021-03-23T13:02:53.000Z","2021-03-23T13:03:11.000Z","2021-03-23T13:15:11.000Z","2021-03-23T16:31:14.000Z","2021-03-23T16:33:07.000Z","2021-03-23T16:34:02.000Z","2021-03-23T16:38:02.000Z","2021-03-23T16:42:02.000Z","2021-03-23T16:46:02.000Z","2021-03-23T16:50:02.000Z","2021-03-23T16:54:02.000Z","2021-03-23T16:58:02.000Z","2021-03-23T17:03:02.000Z","2021-03-23T17:08:02.000Z","2021-03-23T17:13:02.000Z","2021-03-23T17:18:02.000Z","2021-03-23T17:23:03.000Z","2021-03-23T17:28:03.000Z","2021-03-23T17:33:03.000Z","2021-03-23T17:38:03.000Z","2021-03-23T17:43:03.000Z","2021-03-23T17:48:03.000Z","2021-03-23T17:53:03.000Z","2021-03-23T17:58:03.000Z","2021-03-23T18:03:03.000Z","2021-03-23T18:07:03.000Z","2021-03-23T18:11:03.000Z","2021-03-23T18:15:03.000Z","2021-03-23T18:20:03.000Z","2021-03-23T18:24:03.000Z","2021-03-23T18:28:03.000Z","2021-03-23T18:33:04.000Z","2021-03-23T18:37:03.000Z","2021-03-23T18:41:03.000Z","2021-03-23T18:45:03.000Z","2021-03-23T18:50:03.000Z","2021-03-23T18:54:03.000Z","2021-03-23T18:58:03.000Z","2021-03-23T19:02:03.000Z","2021-03-23T19:06:03.000Z","2021-03-23T19:10:03.000Z","2021-03-23T19:14:03.000Z","2021-03-23T19:17:03.000Z","2021-03-23T19:21:03.000Z","2021-03-23T19:25:03.000Z","2021-03-23T19:29:03.000Z","2021-03-23T19:32:03.000Z","2021-03-23T19:36:03.000Z","2021-03-23T19:40:03.000Z","2021-03-23T19:44:03.000Z","2021-03-23T19:47:03.000Z","2021-03-23T19:51:03.000Z","2021-03-23T19:55:03.000Z","2021-03-23T19:59:03.000Z","2021-03-23T20:04:03.000Z","2021-03-23T20:12:03.000Z","2021-03-23T20:19:03.000Z","2021-03-23T20:27:03.000Z","2021-03-23T20:34:04.000Z","2021-03-23T20:42:04.000Z","2021-03-23T20:49:04.000Z","2021-03-23T20:57:04.000Z","2021-03-23T21:05:04.000Z","2021-03-23T21:15:04.000Z","2021-03-23T21:25:04.000Z","2021-03-23T21:33:04.000Z","2021-03-23T21:34:04.000Z","2021-03-23T21:42:04.000Z","2021-03-23T21:49:04.000Z","2021-03-23T21:57:04.000Z","2021-03-23T22:04:04.000Z","2021-03-23T22:12:04.000Z","2021-03-23T22:19:04.000Z","2021-03-23T22:27:04.000Z","2021-03-23T22:34:04.000Z","2021-03-23T22:42:04.000Z","2021-03-23T22:49:04.000Z","2021-03-23T22:57:04.000Z","2021-03-23T23:08:04.000Z","2021-03-23T23:23:04.000Z","2021-03-23T23:38:04.000Z","2021-03-23T23:53:04.000Z","2021-03-24T00:10:04.000Z","2021-03-24T00:30:04.000Z","2021-03-24T00:50:04.000Z","2021-03-24T01:15:05.000Z","2021-03-24T01:45:05.000Z","2021-03-24T03:10:05.000Z","2021-03-24T03:30:05.000Z","2021-03-.......................]

Here is my flow

[{"id":"1886a2b.cd5cf5d","type":"link in","z":"c127f4e1.8d00d8","name":"","links":["e8949d81.48c1e","a95e81c7.e7a9f"],"x":135,"y":100,"wires":[["375b17ee.295778"]]},{"id":"375b17ee.295778","type":"change","z":"c127f4e1.8d00d8","name":"","rules":[{"t":"set","p":"topic","pt":"msg","to":"Aussentemperatur","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":260,"y":100,"wires":[["4c4fe5dc.6420bc"]]},{"id":"fac5b1ef.e204","type":"mysql","z":"c127f4e1.8d00d8","mydb":"265f4698.74824a","name":"","x":570,"y":100,"wires":[["f8ae9231.72ad2"]]},{"id":"bd3751af.2a8b7","type":"mysql","z":"c127f4e1.8d00d8","mydb":"265f4698.74824a","name":"","x":990,"y":100,"wires":[["706a83f1.88416c"]]},{"id":"f8ae9231.72ad2","type":"template","z":"c127f4e1.8d00d8","name":"Gewünschte Abfragewerte","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"SELECT data,topic,timestamp FROM Temperatur","output":"str","x":780,"y":100,"wires":[["bd3751af.2a8b7"]]},{"id":"706a83f1.88416c","type":"function","z":"c127f4e1.8d00d8","name":"data","func":"var data = [];\nvar timestamp = [];\nmsg.payload.forEach(function(value) {\n    data.push(value['data']);\n    timestamp.push(value['timestamp']);\n});\n\nmsg.payload = [{\n    series: \"\",\n    data: [data],\n    labels: [timestamp]\n}];\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":1130,"y":100,"wires":[["47661b07.cc84c4","9a24e6f3.012a28"]]},{"id":"4c4fe5dc.6420bc","type":"function","z":"c127f4e1.8d00d8","name":"ISOTIME","func":"var out = \"INSERT INTO Temperatur (topic,data)\"\nout = out + \"VALUES ('\" \nout = out + msg.topic + \"','\" + msg.payload + \"');\"\n    \nmsg.topic=out;\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":420,"y":100,"wires":[["fac5b1ef.e204"]]},{"id":"28c2a5fc.2ac0aa","type":"comment","z":"c127f4e1.8d00d8","name":"Außentemperatur","info":"","x":260,"y":60,"wires":[]},{"id":"47661b07.cc84c4","type":"link out","z":"c127f4e1.8d00d8","name":"Außentemperatur SQL","links":["f16b0d7d.a1b57"],"x":1235,"y":100,"wires":[]},{"id":"917bf265.497d6","type":"link in","z":"c127f4e1.8d00d8","name":"","links":["d22f2f7d.9098b","7f02611a.f6461"],"x":135,"y":180,"wires":[["7b0490be.ee552"]]},{"id":"7b0490be.ee552","type":"change","z":"c127f4e1.8d00d8","name":"","rules":[{"t":"set","p":"topic","pt":"msg","to":"Feuchtigkeit","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":260,"y":180,"wires":[["bd2aa840.49df28"]]},{"id":"5b1aab63.705e84","type":"mysql","z":"c127f4e1.8d00d8","mydb":"265f4698.74824a","name":"","x":570,"y":180,"wires":[["d31362e5.0b7a"]]},{"id":"fa66dfb4.f782","type":"mysql","z":"c127f4e1.8d00d8","mydb":"265f4698.74824a","name":"","x":990,"y":180,"wires":[["4e3a00cf.51182"]]},{"id":"d31362e5.0b7a","type":"template","z":"c127f4e1.8d00d8","name":"Gewünschte Abfragewerte","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"SELECT data,topic,timestamp FROM Feuchtigkeit","output":"str","x":780,"y":180,"wires":[["fa66dfb4.f782"]]},{"id":"4e3a00cf.51182","type":"function","z":"c127f4e1.8d00d8","name":"data","func":"var data = [];\nvar timestamp = [];\nmsg.payload.forEach(function(value) {\n    data.push(value['data']);\n    timestamp.push(value['timestamp']);\n});\n\nmsg.payload = [{\n    series: \"\",\n    data: [data],\n    labels: timestamp\n}];\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":1130,"y":180,"wires":[["fadee6db.196e28"]]},{"id":"bd2aa840.49df28","type":"function","z":"c127f4e1.8d00d8","name":"ISOTIME","func":"var out = \"INSERT INTO Feuchtigkeit (topic,data)\"\nout = out + \"VALUES ('\" \nout = out + msg.topic + \"','\" + msg.payload + \"');\"\n    \nmsg.topic=out;\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":420,"y":180,"wires":[["5b1aab63.705e84"]]},{"id":"2a7e23b3.b3670c","type":"comment","z":"c127f4e1.8d00d8","name":"Außenfeuchtigkeit","info":"","x":270,"y":140,"wires":[]},{"id":"fadee6db.196e28","type":"link out","z":"c127f4e1.8d00d8","name":"Außenfeuchtigkeit SQL","links":["7fa37e32.ee3b8"],"x":1235,"y":180,"wires":[]},{"id":"9a24e6f3.012a28","type":"debug","z":"c127f4e1.8d00d8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":1300,"y":60,"wires":[]},{"id":"265f4698.74824a","type":"MySQLdatabase","name":"","host":"localhost","port":"3306","db":"wordpress","tz":"local","charset":"UTF8"}]

You need to create an array of objects

[
  {"y":12,"x":1234567899"},
  {"y":13,"x":1234567999"}
]

where y is the temp and x is the timestamp
you could do this with your sql query
SELECT data AS y ,timestamp AS x FROM Temperatur ORDER BY timestamp
This will return an array and rename your keys to x and y in a timestamped order, presuming that data is your temp reading.

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