Lagre og vise energidata i SQL/Node-Red Dashboard.

[SveinHa]

 

Har en tabell som har timelogging av diverse energimålinger:

 

Er det mulig å lage et View som viser verdien fra denne time - verdi forrige time? Altså at tabellen i eksempelet her vises som:

2022-02-16 12:00   HEM   1.550

2022-02-16 13:00   HEM   1.913

2022-02-16 14:00   HEM   1.688

2022-02-16 15:00   HEM   1.864

 

Kan jo mikse og trikse det til på mange måter men et view er jo ganske elegant om det går.

Endret 18. februar 2022 av SveinHa

[SveinHa]

Pingla ut og la til en ekstra kolonne i tabellen samt litt ekstra logikk i Node-Red:

 

Presentert i Node-Red Dashboard slik:

 

For hver sensor jeg vil logge energiforbruket til legges til en linje i venstre del av bildet, resten ordner seg helt selv:

 

[{"id":"3b78f3064e42b34a","type":"comment","z":"06d71aaf3c5ec68d","name":"Lagre kWh i Flow, lagres i SQL hver time","info":"","x":180,"y":30,"wires":[]},{"id":"cd558e34de93cfb0","type":"mqtt in","z":"06d71aaf3c5ec68d","name":"BerederTopp","topic":"zwave/Teknisk/BerederTopp/meter/endpoint_0/value/65537","qos":"2","datatype":"json","broker":"6db118ed1b0c56de","nl":false,"rap":true,"rh":0,"inputs":0,"x":90,"y":80,"wires":[["7e0590efda7c02f1"]]},{"id":"7e0590efda7c02f1","type":"function","z":"06d71aaf3c5ec68d","name":"Parse Value","func":"var svar = \"\";\nvar value=msg.payload.value;\nsvar = {payload:value};\nreturn svar;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":330,"y":80,"wires":[["c3763111bdb5b296"]]},{"id":"c3763111bdb5b296","type":"show-value","z":"06d71aaf3c5ec68d","name":"","path":"","x":500,"y":80,"wires":[["a5f4386273133c36"]]},{"id":"a5f4386273133c36","type":"function","z":"06d71aaf3c5ec68d","name":"Store in Flow","func":"var svar = \"\";\nvar value=msg.payload;\nflow.set(\"BerederTopp\",value)","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":670,"y":80,"wires":[[]]},{"id":"49c14724f263b846","type":"mqtt in","z":"06d71aaf3c5ec68d","name":"Izzo","topic":"zwave/Kjok/Izzo/meter/endpoint_0/value/65537","qos":"2","datatype":"json","broker":"6db118ed1b0c56de","nl":false,"rap":true,"rh":0,"inputs":0,"x":70,"y":130,"wires":[["1101151631de0bf8"]]},{"id":"1101151631de0bf8","type":"function","z":"06d71aaf3c5ec68d","name":"Parse Value","func":"var svar = \"\";\nvar value=msg.payload.value;\nsvar = {payload:value};\nreturn svar;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":330,"y":130,"wires":[["3ae87f29e6c7ebfd"]]},{"id":"3ae87f29e6c7ebfd","type":"show-value","z":"06d71aaf3c5ec68d","name":"","path":"","x":500,"y":130,"wires":[["5df8f831dff859eb"]]},{"id":"5df8f831dff859eb","type":"function","z":"06d71aaf3c5ec68d","name":"Store in Flow","func":"var svar = \"\";\nvar value=msg.payload;\nflow.set(\"Izzo\",value)","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":670,"y":130,"wires":[[]]},{"id":"6724b2b36a9a5114","type":"function","z":"06d71aaf3c5ec68d","name":"Generate SQL","func":"//node.warn(flow.keys());\nflow.keys().forEach(function(element) {\n    if(element.substring(element.length -6,element.length)!=\"_PrevH\"){\n//  node.warn(element);\n//  node.warn(flow.get(element));\n\n    var kWhN = flow.get(element) || 0;\n    var kWhP = flow.get(element + \"_PrevH\") || kWhN;\n    node.warn(element + \": prev: \" + kWhP + \", new: \" + kWhN);\n\n    msg.payload={}\n    msg.payload.Sens=element;\n    msg.payload.kWh=kWhN;\n    msg.payload.kWhDiff=kWhN-kWhP;\n\n    msg.topic = \"INSERT INTO Sensor_kWh (sensor, kWh, kWhDiff) \";\n    msg.topic += \"VALUES ( :Sens, :kWh, :kWhDiff );\"\n    node.send(msg)  //Send til SQL DB\n    //lagre siste timeverdi\n    flow.set(element + \"_PrevH\",kWhN);\n    }\n});\n//return msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":1070,"y":80,"wires":[["6eddebb57f492aba"]]},{"id":"feb7ea9535ae10ff","type":"comment","z":"06d71aaf3c5ec68d","name":"Lagre kWh i SQL-DB hver time, tar alle verdier i context.Flow","info":"","x":1010,"y":30,"wires":[]},{"id":"647085b9fd971e78","type":"cronplus","z":"06d71aaf3c5ec68d","name":"Hourly","outputField":"payload","timeZone":"","persistDynamic":false,"commandResponseMsgOutput":"output1","outputs":1,"options":[{"name":"schedule1","topic":"schedule1","payloadType":"default","payload":"","expressionType":"cron","expression":"0 0 * * * ? *","location":"","offset":"0","solarType":"all","solarEvents":"sunrise,sunset"}],"x":860,"y":80,"wires":[["6724b2b36a9a5114"]]},{"id":"6eddebb57f492aba","type":"Stackhero-MySQL","z":"06d71aaf3c5ec68d","server":"c919f77e07994edc","name":"","x":1260,"y":80,"wires":[[]]},{"id":"b02e55c7c71471e9","type":"mqtt in","z":"06d71aaf3c5ec68d","name":"Sov2Ovn","topic":"zwave/Sov2/Ovn/meter/endpoint_0/value/65537","qos":"2","datatype":"json","broker":"6db118ed1b0c56de","nl":false,"rap":true,"rh":0,"inputs":0,"x":80,"y":180,"wires":[["fb03a3e8c0e1743b"]]},{"id":"fb03a3e8c0e1743b","type":"function","z":"06d71aaf3c5ec68d","name":"Parse Value","func":"var svar = \"\";\nvar value=msg.payload.value;\nsvar = {payload:value};\nreturn svar;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":330,"y":180,"wires":[["a9763424a4a41b61"]]},{"id":"a9763424a4a41b61","type":"show-value","z":"06d71aaf3c5ec68d","name":"","path":"","x":500,"y":180,"wires":[["ee24446c589f2eea"]]},{"id":"ee24446c589f2eea","type":"function","z":"06d71aaf3c5ec68d","name":"Store in Flow","func":"var svar = \"\";\nvar value=msg.payload;\nflow.set(\"Sov2Ovn\",value)","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":670,"y":180,"wires":[[]]},{"id":"6db118ed1b0c56de","type":"mqtt-broker","name":"DaleMQTT","broker":"172.16.0.94","port":"1883","clientid":"","autoConnect":true,"usetls":false,"protocolVersion":"5","keepalive":"60","cleansession":true,"birthTopic":"","birthQos":"0","birthPayload":"","birthMsg":{},"closeTopic":"","closeQos":"0","closePayload":"","closeMsg":{},"willTopic":"","willQos":"0","willPayload":"","willMsg":{},"sessionExpiry":""},{"id":"c919f77e07994edc","type":"Stackhero-MySQL-Server","name":"","host":"172.16.0.98","port":"3306","tls":false,"database":"NodeRedEnergi"}]

 

Noder for visning i Dashboard (der ligger mye rot i scriptene fordi det er samme script jeg har brukt mange ganger og kopiert og en del er med som mine huskelapper men det er ikke store greiene og essensen er bare et 4 linjer):

[{"id":"c0453f143869605d","type":"ui_table","z":"d49bfe3335c3f7c9","group":"74a4fc478126be80","name":"Totaler i dag","order":5,"width":"10","height":"6","columns":[],"outputs":0,"cts":false,"x":990,"y":1560,"wires":[]},{"id":"f4b627e0f6841ee6","type":"function","z":"d49bfe3335c3f7c9","name":"kWh totaler i dag","func":"\nvar d = new Date();\nd.setDate(d.getDate() - 0);\nvar pd = new Date();\npd.setDate(pd.getDate() - 1);\n\nlet year = d.getFullYear();\nlet month = d.getMonth()+1;\nlet day = d.getDate()+1;\n\nlet pyear = pd.getFullYear();\nlet pmonth = pd.getMonth()+1;\nlet pday = pd.getDate()+1;\n\nlet dbts = (year*10000000000)+(month*100000000)+(day*1000000);\nlet pdbts = (pyear*10000000000)+(pmonth*100000000)+(pday*1000000);\n\nmsg.payload={}\n\n//SELECT Sensor, SUM(kWhDiff) FROM `Sensor_kWh` WHERE 1 GROUP BY Sensor\n\n//msg.topic = \"SELECT MID(Timestamp,9,4) AS 'Tid i forgårs', kWh_hour AS kWh, MaxPower/1000 AS 'Max kW', CurrentPrice AS Pris FROM Energi \";\nmsg.topic = \"SELECT MID(Timestamp,1,10) AS 'I dag', Sensor, SUM(kWhDiff) AS kWh FROM Sensor_kWh \";\nmsg.topic += \"WHERE Timestamp >= \" + pdbts + \" AND Timestamp < \" + dbts;\nmsg.topic += \" GROUP BY Sensor\";\nmsg.topic += \" ORDER BY Sensor ASC\";\n//msg.topic += ', ' + verdi\n//msg.topic += ')'\nreturn msg;\n\n\n/*\nmsg.payload={}\nmsg.payload.userToChange=42;\nmsg.payload.newUsername=\"example-user\";\nmsg.topic=\"INSERT INTO users (`userid`, `username`) VALUES (:userToChange, :newUsername) ON DUPLICATE KEY UPDATE `username`=:newUsername;\"\nreturn msg;\n\nvar time = new Date();\nvar displaytime = time.toLocaleTimeString();\nvar hour = time.getHours();\n\n*/","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":520,"y":1560,"wires":[["127d30d63e638a30"]]},{"id":"127d30d63e638a30","type":"Stackhero-MySQL","z":"d49bfe3335c3f7c9","server":"c919f77e07994edc","name":"","x":800,"y":1560,"wires":[["c0453f143869605d"]]},{"id":"4a1e829292118e60","type":"ui_table","z":"d49bfe3335c3f7c9","group":"74a4fc478126be80","name":"Totaler i går","order":6,"width":"10","height":"6","columns":[],"outputs":0,"cts":false,"x":990,"y":1600,"wires":[]},{"id":"efb1481331c0cdf4","type":"function","z":"d49bfe3335c3f7c9","name":"kWh totaler i går","func":"\nvar d = new Date();\nd.setDate(d.getDate() - 1);\nvar pd = new Date();\npd.setDate(pd.getDate() - 2);\n\nlet year = d.getFullYear();\nlet month = d.getMonth()+1;\nlet day = d.getDate()+1;\n\nlet pyear = pd.getFullYear();\nlet pmonth = pd.getMonth()+1;\nlet pday = pd.getDate()+1;\n\nlet dbts = (year*10000000000)+(month*100000000)+(day*1000000);\nlet pdbts = (pyear*10000000000)+(pmonth*100000000)+(pday*1000000);\n\nmsg.payload={}\n\n//SELECT Sensor, SUM(kWhDiff) FROM `Sensor_kWh` WHERE 1 GROUP BY Sensor\n\n//msg.topic = \"SELECT MID(Timestamp,9,4) AS 'Tid i forgårs', kWh_hour AS kWh, MaxPower/1000 AS 'Max kW', CurrentPrice AS Pris FROM Energi \";\nmsg.topic = \"SELECT MID(Timestamp,1,10) AS 'I går', Sensor, SUM(kWhDiff) AS kWh FROM Sensor_kWh \";\nmsg.topic += \"WHERE Timestamp >= \" + pdbts + \" AND Timestamp < \" + dbts;\nmsg.topic += \" GROUP BY Sensor\";\nmsg.topic += \" ORDER BY Sensor ASC\";\n//msg.topic += ', ' + verdi\n//msg.topic += ')'\nreturn msg;\n\n\n/*\nmsg.payload={}\nmsg.payload.userToChange=42;\nmsg.payload.newUsername=\"example-user\";\nmsg.topic=\"INSERT INTO users (`userid`, `username`) VALUES (:userToChange, :newUsername) ON DUPLICATE KEY UPDATE `username`=:newUsername;\"\nreturn msg;\n\nvar time = new Date();\nvar displaytime = time.toLocaleTimeString();\nvar hour = time.getHours();\n\n*/","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":510,"y":1600,"wires":[["608a377c3c209038"]]},{"id":"608a377c3c209038","type":"Stackhero-MySQL","z":"d49bfe3335c3f7c9","server":"c919f77e07994edc","name":"","x":800,"y":1600,"wires":[["4a1e829292118e60"]]},{"id":"6bdcc832f4abdf36","type":"ui_table","z":"d49bfe3335c3f7c9","group":"74a4fc478126be80","name":"Totaler Mnd","order":9,"width":"10","height":"6","columns":[],"outputs":0,"cts":false,"x":990,"y":1640,"wires":[]},{"id":"04f53c90f3499a4f","type":"function","z":"d49bfe3335c3f7c9","name":"kWh månedstotaler pr sensor","func":"\nvar d = new Date();\nd.setDate(d.getDate() - 0);\nvar pd = new Date();\npd.setDate(pd.getDate() - 31);\n\nlet year = d.getFullYear();\nlet month = d.getMonth()+1;\nlet day = d.getDate()+1;\n\nlet pyear = pd.getFullYear();\nlet pmonth = pd.getMonth()+1;\nlet pday = pd.getDate()+1;\n\nlet dbts = (year*10000000000)+(month*100000000)+(day*1000000);\nlet pdbts = (pyear*10000000000)+(pmonth*100000000)+(pday*1000000);\n\nmsg.payload={}\n\n//SELECT Sensor, SUM(kWhDiff) FROM `Sensor_kWh` WHERE 1 GROUP BY Sensor\n\n//msg.topic = \"SELECT MID(Timestamp,9,4) AS 'Tid i forgårs', kWh_hour AS kWh, MaxPower/1000 AS 'Max kW', CurrentPrice AS Pris FROM Energi \";\nmsg.topic = \"SELECT MID(Timestamp,1,7) AS 'Månedstotal', Sensor, SUM(kWhDiff) AS kWh FROM Sensor_kWh \";\nmsg.topic += \"WHERE Timestamp >= \" + pdbts + \" AND Timestamp < \" + dbts;\nmsg.topic += \" GROUP BY MID(Timestamp,1,7), Sensor\";\nmsg.topic += \" ORDER BY Sensor ASC\";\n//msg.topic += ', ' + verdi\n//msg.topic += ')'\nreturn msg;\n\n\n/*\nmsg.payload={}\nmsg.payload.userToChange=42;\nmsg.payload.newUsername=\"example-user\";\nmsg.topic=\"INSERT INTO users (`userid`, `username`) VALUES (:userToChange, :newUsername) ON DUPLICATE KEY UPDATE `username`=:newUsername;\"\nreturn msg;\n\nvar time = new Date();\nvar displaytime = time.toLocaleTimeString();\nvar hour = time.getHours();\n\n*/","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":550,"y":1640,"wires":[["ac8a86079c0c320f"]]},{"id":"ac8a86079c0c320f","type":"Stackhero-MySQL","z":"d49bfe3335c3f7c9","server":"c919f77e07994edc","name":"","x":800,"y":1640,"wires":[["6bdcc832f4abdf36"]]},{"id":"bc9b688fb34f3e05","type":"ui_table","z":"d49bfe3335c3f7c9","group":"74a4fc478126be80","name":"Totaler År","order":9,"width":"10","height":"6","columns":[],"outputs":0,"cts":false,"x":980,"y":1680,"wires":[]},{"id":"fe0bc0da5a06087c","type":"function","z":"d49bfe3335c3f7c9","name":"kWh årstotaler pr sensor","func":"\nvar d = new Date();\nd.setDate(d.getDate() - 0);\nvar pd = new Date();\npd.setDate(pd.getDate() - 31);\n\nlet year = d.getFullYear();\nlet month = d.getMonth()+1;\nlet day = d.getDate()+1;\n\nlet pyear = pd.getFullYear();\nlet pmonth = pd.getMonth()+1;\nlet pday = pd.getDate()+1;\n\nlet dbts = (year*10000000000)+(month*100000000)+(day*1000000);\nlet pdbts = (pyear*10000000000)+(pmonth*100000000)+(pday*1000000);\n\nmsg.payload={}\n\n//SELECT Sensor, SUM(kWhDiff) FROM `Sensor_kWh` WHERE 1 GROUP BY Sensor\n\n//msg.topic = \"SELECT MID(Timestamp,9,4) AS 'Tid i forgårs', kWh_hour AS kWh, MaxPower/1000 AS 'Max kW', CurrentPrice AS Pris FROM Energi \";\nmsg.topic = \"SELECT MID(Timestamp,1,4) AS 'Årstotal', Sensor, SUM(kWhDiff) AS kWh FROM Sensor_kWh \";\nmsg.topic += \"WHERE 1\";\nmsg.topic += \" GROUP BY MID(Timestamp,1,4), Sensor\";\nmsg.topic += \" ORDER BY Sensor ASC\";\n//msg.topic += ', ' + verdi\n//msg.topic += ')'\nreturn msg;\n\n\n/*\nmsg.payload={}\nmsg.payload.userToChange=42;\nmsg.payload.newUsername=\"example-user\";\nmsg.topic=\"INSERT INTO users (`userid`, `username`) VALUES (:userToChange, :newUsername) ON DUPLICATE KEY UPDATE `username`=:newUsername;\"\nreturn msg;\n\nvar time = new Date();\nvar displaytime = time.toLocaleTimeString();\nvar hour = time.getHours();\n\n*/","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":540,"y":1680,"wires":[["9c1c8a6fee901041"]]},{"id":"9c1c8a6fee901041","type":"Stackhero-MySQL","z":"d49bfe3335c3f7c9","server":"c919f77e07994edc","name":"","x":800,"y":1680,"wires":[["bc9b688fb34f3e05"]]},{"id":"74a4fc478126be80","type":"ui_group","name":"Total pr sensor","tab":"bbe9ae4be8e5f235","order":4,"disp":true,"width":"10","collapse":false,"className":""},{"id":"c919f77e07994edc","type":"Stackhero-MySQL-Server","name":"","host":"172.16.0.98","port":"3306","tls":false,"database":"NodeRedEnergi"},{"id":"bbe9ae4be8e5f235","type":"ui_tab","name":"Energi DB","icon":"receipt","order":2,"disabled":false,"hidden":false}]

 

Endret 18. februar 2022 av SveinHa