Hi Jerry,
The important thing is what Colin said .. to use a Join node.
Because the MQTT messages arrive at different times .. you need to use the Join node to wait and gather all 4 msgs and then send them to your function so the values can be simultaneously available.
After that, the Function node can store the mqtt values to JS variables in order to construct the sql query in msg.topic
// fields ID, DATETIME, TEMP, HUM, PRES, VOLT
let temp = msg.payload["/swa/temperature"]
let hum = msg.payload["/swa/hum"]
let pres = msg.payload["/swa/pres"]
let volt = msg.payload["/swa/volt"]
msg.topic = `INSERT INTO IOTSensors (DATETIME, TEMP, HUM, PRES, VOLT) VALUES (datetime('now'), ${temp}, ${hum}, ${pres}, ${volt})`;
return msg
Test Flow :
[{"id":"296aeedb.892322","type":"mqtt in","z":"2beed822.5ff3d8","name":"","topic":"/swa/temperature","qos":"1","datatype":"json","broker":"a24c936.fc6627","x":160,"y":100,"wires":[["b33b5a6b.b8873"]]},{"id":"939c5659.af85c8","type":"mqtt in","z":"2beed822.5ff3d8","name":"","topic":"/swa/humidity","qos":"1","datatype":"json","broker":"a24c936.fc6627","x":150,"y":200,"wires":[["b33b5a6b.b8873"]]},{"id":"e03bbfcc.00ff3","type":"mqtt in","z":"2beed822.5ff3d8","name":"","topic":"/swa/pressure","qos":"1","datatype":"json","broker":"a24c936.fc6627","x":150,"y":300,"wires":[["b33b5a6b.b8873"]]},{"id":"a73a9c2b.0ff6","type":"mqtt in","z":"2beed822.5ff3d8","name":"volt","topic":"/swa/volt","qos":"2","datatype":"json","broker":"a24c936.fc6627","x":130,"y":400,"wires":[["b33b5a6b.b8873"]]},{"id":"f934f9df.0f3d68","type":"function","z":"2beed822.5ff3d8","name":"Add Time","func":"\nmsg.payload = msg.payload + new Date().toString();\n\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":640,"y":480,"wires":[["4e7fb055.3757e"]]},{"id":"4e7fb055.3757e","type":"file","z":"2beed822.5ff3d8","name":"","filename":"/mnt/hdd/data.log","appendNewline":true,"createDir":true,"overwriteFile":"false","encoding":"none","x":850,"y":480,"wires":[[]]},{"id":"6d234b04.048054","type":"function","z":"2beed822.5ff3d8","name":"","func":"//var sqliteTimeStamp = Math.round(Date.now() / 1000); //converting to seconds instead of milliseconds epoch;\n\n//var theSQL = \"INSERT INTO IOTSensors (timestamp, deviceName, sensor, reading) VALUES \";\n//theSQL = theSQL + \"('\" + sqliteTimeStamp + \"', '\" + theDevice +\"', '\" + theSensor + \"', \" + msg.payload + \");\";\n\nlet theDevice = \"ESP8266\";\n\n// fields ID, DATETIME, TEMP, HUM, PRES, VOLT\nlet temp = msg.payload[\"/swa/temperature\"]\nlet hum = msg.payload[\"/swa/hum\"]\nlet pres = msg.payload[\"/swa/pres\"]\nlet volt = msg.payload[\"/swa/volt\"]\n\nmsg.topic = `INSERT INTO IOTSensors (DATETIME, TEMP, HUM, PRES, VOLT) VALUES (datetime('now'), ${temp}, ${hum}, ${pres}, ${volt})`;\n\nreturn msg","outputs":1,"noerr":0,"initialize":"","finalize":"","x":660,"y":240,"wires":[["8fc80f1a.99156","77445301.37fb74"]]},{"id":"8fc80f1a.99156","type":"sqlite","z":"2beed822.5ff3d8","mydb":"f7816a86.b66188","sqlquery":"msg.topic","sql":"","name":"Data","x":930,"y":240,"wires":[[]]},{"id":"4b03aa41.f9d284","type":"debug","z":"2beed822.5ff3d8","name":"1","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":490,"y":160,"wires":[]},{"id":"b33b5a6b.b8873","type":"join","z":"2beed822.5ff3d8","name":"","mode":"custom","build":"object","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":false,"timeout":"","count":"4","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":390,"y":240,"wires":[["4b03aa41.f9d284","6d234b04.048054"]]},{"id":"eb432989.b559a","type":"mqtt out","z":"2beed822.5ff3d8","name":"","topic":"/swa/temperature","qos":"1","retain":"","broker":"a24c936.fc6627","x":470,"y":680,"wires":[]},{"id":"96aaf19a.1a16e8","type":"inject","z":"2beed822.5ff3d8","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"23","payloadType":"num","x":190,"y":680,"wires":[["eb432989.b559a"]]},{"id":"e52edafc.6d4048","type":"mqtt out","z":"2beed822.5ff3d8","name":"","topic":"/swa/humidity","qos":"1","retain":"","broker":"a24c936.fc6627","x":450,"y":780,"wires":[]},{"id":"1344f036.91e1d","type":"inject","z":"2beed822.5ff3d8","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"60","payloadType":"num","x":180,"y":780,"wires":[["e52edafc.6d4048"]]},{"id":"acf7ebc5.acb5b","type":"comment","z":"2beed822.5ff3d8","name":"Simulate MQTT message from device","info":"","x":350,"y":580,"wires":[]},{"id":"5b55c6e4.df491","type":"mqtt out","z":"2beed822.5ff3d8","name":"","topic":"/swa/pressure","qos":"1","retain":"","broker":"a24c936.fc6627","x":440,"y":880,"wires":[]},{"id":"370d0bb8.cdfd8c","type":"inject","z":"2beed822.5ff3d8","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"2.3","payloadType":"num","x":170,"y":880,"wires":[["5b55c6e4.df491"]]},{"id":"b2f7352.09871c8","type":"mqtt out","z":"2beed822.5ff3d8","name":"","topic":"/swa/volt","qos":"1","retain":"","broker":"a24c936.fc6627","x":410,"y":980,"wires":[]},{"id":"f209598e.3f8988","type":"inject","z":"2beed822.5ff3d8","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"233.12","payloadType":"str","x":160,"y":980,"wires":[["b2f7352.09871c8"]]},{"id":"77445301.37fb74","type":"debug","z":"2beed822.5ff3d8","name":"2","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":790,"y":160,"wires":[]},{"id":"a24c936.fc6627","type":"mqtt-broker","name":"BME280","broker":"localhost","port":"1883","clientid":"","usetls":false,"compatmode":false,"keepalive":"60","cleansession":true,"birthTopic":"","birthQos":"1","birthPayload":"","closeTopic":"","closeQos":"0","closePayload":"","willTopic":"","willQos":"0","willPayload":""},{"id":"f7816a86.b66188","type":"sqlitedb","z":"2beed822.5ff3d8","db":"IOT.db","mode":"RWC"}]
