[{"id":"21713bc1.4cc3b4","type":"tab","label":"my edit to Databse","disabled":false,"info":""},{"id":"43089f40.837d1","type":"inject","z":"21713bc1.4cc3b4","name":"create database table","topic":"CREATE TABLE data(TIMESTAMP INT PRIMARY KEY NOT NULL, SENSOR TEXT NOT NULL, TEMPERATURE INT NOT NULL, HUMIDITY INT NOT NULL)","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":"","x":205.0833282470703,"y":137.08334350585938,"wires":[["fac0809e.d434c"]]},{"id":"1ba18632.d0e84a","type":"debug","z":"21713bc1.4cc3b4","name":"database_out","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":876.0833129882812,"y":269.0833740234375,"wires":[]},{"id":"7adebdc0.75faec","type":"inject","z":"21713bc1.4cc3b4","name":"insert data","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":"","x":139.0833282470703,"y":382.0833511352539,"wires":[["85c2080e.837828"]]},{"id":"ad76b8a3.459d48","type":"inject","z":"21713bc1.4cc3b4","name":"delete records","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":"","x":182.0833282470703,"y":218.0833511352539,"wires":[["c9dad696.1a7438"]]},{"id":"40464f7b.cd1a78","type":"inject","z":"21713bc1.4cc3b4","name":"show RECORDS","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":true,"onceDelay":"","x":141.0833282470703,"y":530.0833435058594,"wires":[["9805579b.0455d8"]]},{"id":"3dca9a55.49ff36","type":"inject","z":"21713bc1.4cc3b4","name":"Test Data","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":114.08332824707031,"y":660.0834121704102,"wires":[["5294f53.104b98c"]]},{"id":"1a8f6865.49af7","type":"function","z":"21713bc1.4cc3b4","name":"create test data","func":"var count=context.get('count');\nvar timestamp=context.get('timestamp');\n\nif (count===undefined)\n{\n count=1;\n //timestamp=1;\n}\nvar data1={\"sensor\":\"sensor1\",\"temperature\":21,\"humidity\":61};\n//var data2={\"sensor\":\"sensor2\",\"temperature\":22,\"humidity\":62};\n//var data3={\"sensor\":\"sensor3\",\"temperature\":23,\"humidity\":63};\nif (count%20===0)\n count=-count;\ndata1.temperature+=(count*.25);\n//data2.temperature+=(count*.5);\n//data3.temperature+=(count*.75);\ndata1.humidity+=(count*.25);\n//data2.humidity+=(count*.5);\n//data3.humidity+=(count*.75);\nmsg.payload=[data1];\nvar msg1={};\nvar msg2={};\nvar msg3={};\n\ndata1.timestamp=msg.timestamp;\n//timestamp+=1;\n//data2.timestamp=timestamp;\n//timestamp+=1;\n//data3.timestamp=timestamp;\n\nmsg1.payload=data1;\n//msg2.payload=data2;\n//msg3.payload=data3;\n//timestamp+=1;\ncount+=1;\ncontext.set('count',count);\ncontext.set('timestamp',timestamp);\n\n\nreturn msg1;","outputs":1,"noerr":0,"x":231.0833282470703,"y":715.0834121704102,"wires":[["66733ab6.e3842c","8d024dd.dbca6b"]]},{"id":"66733ab6.e3842c","type":"debug","z":"21713bc1.4cc3b4","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":484.08331298828125,"y":717.0833740234375,"wires":[]},{"id":"8d024dd.dbca6b","type":"function","z":"21713bc1.4cc3b4","name":"write query","func":"var timestamp=msg.payload.timestamp;\nvar sensor=msg.payload.sensor;\nvar temperature=msg.payload.temperature;\nvar humidity=msg.payload.humidity;\n//put data into json string\n\nmsg=\"(timestamp,sensor,temperature,humidity) values(\\'\"+timestamp +\"\\',\\'\"+sensor +\"\\',\" + temperature + \",\" + humidity +\")\";\nvar topic=\"INSERT INTO DATA \" +msg;\nvar msg1={};\nmsg1.topic=topic;\nmsg1.payload=timestamp;\nreturn msg1;","outputs":1,"noerr":0,"x":326.08331298828125,"y":667.0833740234375,"wires":[["54fe078.61ffb78","5dcdd59.7920dac"]]},{"id":"54fe078.61ffb78","type":"debug","z":"21713bc1.4cc3b4","name":"sql out","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":508.08331298828125,"y":665.0833435058594,"wires":[]},{"id":"85c2080e.837828","type":"function","z":"21713bc1.4cc3b4","name":"Insert using variable","func":"var sensor=\"sensor5\";\nmsg=\"( timestamp,sensor,temperature,humidity) values(4,\"+\"\\'\"+sensor +\"\\'\" + \",20,66)\";\nvar topic=\"INSERT INTO DATA \" +msg;\nvar msg1={};\nmsg1.topic=topic;\n\nreturn msg1;","outputs":1,"noerr":0,"x":332.08331298828125,"y":380.0833511352539,"wires":[["46459ea6.e60f6","3f3087a5.6a372"]]},{"id":"46459ea6.e60f6","type":"debug","z":"21713bc1.4cc3b4","name":"insert","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":800.0833129882812,"y":373.0833511352539,"wires":[]},{"id":"ac7bd110.0e9b5","type":"inject","z":"21713bc1.4cc3b4","name":"Drop Table data","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":"","x":191.0833282470703,"y":305.0833511352539,"wires":[["d5f4d33a.c7bd68"]]},{"id":"75c64efb.b96ae","type":"inject","z":"21713bc1.4cc3b4","name":"insert","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":161.0833282470703,"y":263.0833511352539,"wires":[["88eebeac.354658"]]},{"id":"4e7cb534.77dba4","type":"mysql","z":"21713bc1.4cc3b4","mydb":"13a851ac.267e3e","name":"","x":646.2499389648438,"y":125.75,"wires":[["5868d455.b4c894"]]},{"id":"3f3087a5.6a372","type":"mysql","z":"21713bc1.4cc3b4","mydb":"13a851ac.267e3e","name":"","x":650.1666259765625,"y":266.1666946411133,"wires":[["1ba18632.d0e84a"]]},{"id":"5868d455.b4c894","type":"debug","z":"21713bc1.4cc3b4","name":"insert","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":868.1666259765625,"y":133.16668701171875,"wires":[]},{"id":"88eebeac.354658","type":"sqlstring-format","z":"21713bc1.4cc3b4","name":"insert into data","query":"INSERT INTO data( timestamp,sensor,temperature,humidity)\nvalues(0,'sensor',20,66)","vars":"","outField":"topic","x":413.1666259765625,"y":260.1666946411133,"wires":[["3f3087a5.6a372"]]},{"id":"d5f4d33a.c7bd68","type":"sqlstring-format","z":"21713bc1.4cc3b4","name":"DROP Table data","query":"DROP TABLE data","vars":"","outField":"topic","x":422.16656494140625,"y":301.1666946411133,"wires":[["3f3087a5.6a372"]]},{"id":"a408f4a0.3fc578","type":"catch","z":"21713bc1.4cc3b4","name":"","scope":null,"uncaught":false,"x":91.08332824707031,"y":796.083381652832,"wires":[["acf3628d.6d4fa","316dc356.a69a3c"]]},{"id":"acf3628d.6d4fa","type":"debug","z":"21713bc1.4cc3b4","name":"error","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":232.0833282470703,"y":841.083381652832,"wires":[]},{"id":"9912f1b3.f04b7","type":"ui_text","z":"21713bc1.4cc3b4","group":"9292b504.948de","order":7,"width":0,"height":0,"name":"","label":"text","format":"{{msg.payload}}","layout":"row-spread","x":389.08331298828125,"y":802.083381652832,"wires":[\*]},{"id":"316dc356.a69a3c","type":"function","z":"21713bc1.4cc3b4","name":"sql errors","func":"var payload=msg.error.message;\nmsg.payload = payload +\" timestamp \"+msg.payload;\nreturn msg;","outputs":1,"noerr":0,"x":242.08331298828125,"y":802.083381652832,"wires":[["9912f1b3.f04b7"]]},{"id":"c1a92b53.40ece8","type":"function","z":"21713bc1.4cc3b4","name":"Clear errors","func":"node.error(\"\",msg);//clear error\nreturn msg;","outputs":1,"noerr":0,"x":292.08331298828125,"y":892.083381652832,"wires":[[]]},{"id":"67e139af.63786","type":"inject","z":"21713bc1.4cc3b4","name":"clear errors","topic":"","payload":"","payloadType":"str","repeat":"","crontab":"","once":true,"onceDelay":0.1,"x":114.08332824707031,"y":892.0834121704102,"wires":[["c1a92b53.40ece8"]]},{"id":"2ccfae60.38c16a","type":"inject","z":"21713bc1.4cc3b4","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":145.0833282470703,"y":1419.0833435058594,"wires":[["eabf21a9.283dc"]]},{"id":"eabf21a9.283dc","type":"function","z":"21713bc1.4cc3b4","name":"","func":"var data={};\ndata[\"temp\"]=20;\ndata[\"humidity\"]=50;\njson_out=JSON.stringify(data);\nreturn msg;","outputs":1,"noerr":0,"x":315.0833282470703,"y":1459.0833435058594,"wires":[[]]},{"id":"c9dad696.1a7438","type":"sqlstring-format","z":"21713bc1.4cc3b4","name":"delete records","query":"DELETE FROM DATA WHERE count>=0","vars":"","outField":"topic","x":413.08331298828125,"y":216.0833511352539,"wires":[["3f3087a5.6a372","5dcdd59.7920dac"]]},{"id":"fac0809e.d434c","type":"sqlstring-format","z":"21713bc1.4cc3b4","name":"create database table","query":"USE node_red;\nCREATE TABLE data(\n\t\t\tcount MEDIUMINT PRIMARY KEY NOT NULL AUTO_INCREMENT,\n\t\t\tTIMESTAMP TIMESTAMP NOT NULL,\n\t\t\tSENSOR TEXT NOT NULL,\n\t\t\tTEMPERATURE INT NOT NULL,\n\t\t\tHUMIDITY INT NOT NULL);","vars":"","outField":"topic","x":435.08331298828125,"y":129.08334350585938,"wires":[["3f3087a5.6a372"]]},{"id":"774c7446.99e52c","type":"sqlstring-format","z":"21713bc1.4cc3b4","name":"Show records","query":"SELECT * FROM data","vars":"","outField":"topic","x":607.0833129882812,"y":534.0833435058594,"wires":[["5dcdd59.7920dac"]]},{"id":"48ca50a2.b9fe2","type":"inject","z":"21713bc1.4cc3b4","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":770.3666381835938,"y":429,"wires":[["46459ea6.e60f6"]]},{"id":"387c0cbb.019a9c","type":"moment","z":"21713bc1.4cc3b4","name":"Month-DD hh:mm:sss","topic":"","input":"","inputType":"date","inTz":"Africa/Johannesburg","adjAmount":0,"adjType":"days","adjDir":"add","format":"MMM-DD hh:mm:sss","locale":"en_ZA","output":"timestamp","outputType":"msg","outTz":"Africa/Johannesburg","x":443.36663818359375,"y":62.666648864746094,"wires":[["46459ea6.e60f6"]]},{"id":"f3d1e7a9.70cd1","type":"inject","z":"21713bc1.4cc3b4","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":182.3666534423828,"y":58,"wires":[["387c0cbb.019a9c"]]},{"id":"5294f53.104b98c","type":"moment","z":"21713bc1.4cc3b4","name":"YYYY-MM-DD hh:mm:ss","topic":"","input":"","inputType":"date","inTz":"Africa/Johannesburg","adjAmount":0,"adjType":"days","adjDir":"add","format":"YYYY-MM-DD hh:mm:ss","locale":"en_ZA","output":"timestamp","outputType":"msg","outTz":"Africa/Johannesburg","x":221.0833282470703,"y":607.0833435058594,"wires":[["1a8f6865.49af7","c312674a.c6a888"]]},{"id":"c312674a.c6a888","type":"debug","z":"21713bc1.4cc3b4","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":483.08331298828125,"y":604.0833435058594,"wires":[]},{"id":"5dcdd59.7920dac","type":"mysql","z":"21713bc1.4cc3b4","mydb":"13a851ac.267e3e","name":"","x":692.0833129882812,"y":677.583251953125,"wires":[["2f7a966f.902e1a","32e4f64.50e570a"]]},{"id":"2f7a966f.902e1a","type":"ui_template","z":"21713bc1.4cc3b4","group":"9292b504.948de","name":"UI Table","order":1,"width":"","height":"","format":"<style>\n.table\n{\n height:400px;\n width:1000px;\n background:lightblue;\n}\n</style>\n<div class=\"table\">\n<table style=\"width:100%\">\n <tr>\n <th>count</th> \n <th>TIMESTAMP</th> \n <th>SENSOR</th>\n <th>TEMPERATURE</th> \n <th>HUMIDITY</th>\n \n </tr>\n <tr ng-repeat=\"x in msg.payload | limitTo:10\">\n <td>{{msg.payload[$index].count}}</td>\n <td>{{msg.payload[$index].TIMESTAMP}}</td>\n <td>{{msg.payload[$index].SENSOR}}</td>\n <td>{{msg.payload[$index].TEMPERATURE}}</td> \n <td>{{msg.payload[$index].HUMIDITY}}</td>\n\n </tr>\n</table>\n</div>","storeOutMessages":true,"fwdInMessages":true,"templateScope":"local","x":899.0833129882812,"y":671.0833129882812,"wires":[["1a54d24e.1c813e"]]},{"id":"32e4f64.50e570a","type":"debug","z":"21713bc1.4cc3b4","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":864.0833129882812,"y":733.0833129882812,"wires":[]},{"id":"1a54d24e.1c813e","type":"debug","z":"21713bc1.4cc3b4","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":1070.0833740234375,"y":671.5833129882812,"wires":[]},{"id":"9805579b.0455d8","type":"moment","z":"21713bc1.4cc3b4","name":"YYYY-MM-DD hh:mm:ss","topic":"","input":"","inputType":"date","inTz":"Africa/Johannesburg","adjAmount":0,"adjType":"days","adjDir":"add","format":"YYYY-MM-DD hh:mm:ss","locale":"en_ZA","output":"timestamp","outputType":"msg","outTz":"Africa/Johannesburg","x":371.08331298828125,"y":525.5833282470703,"wires":[["774c7446.99e52c"]]},{"id":"13a851ac.267e3e","type":"MySQLdatabase","z":"","host":"localhost","port":"3306","db":"node_red","tz":"GMT+2"},{"id":"9292b504.948de","type":"ui_group","z":"","name":"Sensors","tab":"b52510cb.895308","order":1,"disp":true,"width":"12","collapse":false},{"id":"b52510cb.895308","type":"ui_tab","z":"","name":"Home","icon":"dashboard"}]
high please use code above to follow my flows.
I create timestamps in node-red using the moment node.
I insert these into a TABLE called data using the correct mySQL TIMESTAMP format/syntax and all is well. Although when I query it back from the database is returns the columns in msg.payload array, as it should, but the timestamp in the array is a different time value to what I put in.
in the pink box i use [SELECT * FROM data] to query the data table for all columns. I have circle in pink the timestamp that I am uncertain of where it comes from because in mySQL table it is not stored in this way. refer to next image.
circled in blue is the exact way it is stored in mySQL TIMESTAMP column
where is the TIMESTAMP: "2020-03-17T07:35:38.000Z"
coming from?
