I have re-made my function block to insert 200 data in 1Hz. It works. Thx. However, I was using now(6) for mysql but then, I got 200 entries with the same time. Is there any easy way in node-red to create the datetime in this format?
2019-09-01 02:26:34.779242 which is the same as now(6) in mySQL? Thx
I would try just using Date.now() and leaving it as a javascript date. Is suspect that the SQL node will convert that correctly.
2019/9/1 下午5:53:40node: MYSQL_liftcarmsg : error
"Error: ER_TRUNCATED_WRONG_VALUE: Incorrect datetime value: '1567331622143' for column 'RealTime' at row 1"
I made some change by adding a timestamp node (repeat at 0.001s) + moment node to create a global value which the the time I need. However, when I use this global value to attach to my serial signal which comes at 0.005s, mySQL complained there is a duplicated entry of time...
[{"id":"e77fb21.e5d235","type":"tab","label":"Flow 1","disabled":false,"info":""},{"id":"a1cdf489.9fa3f8","type":"inject","z":"e77fb21.e5d235","name":"","topic":"","payload":"","payloadType":"date","repeat":"0.001","crontab":"","once":false,"onceDelay":0.1,"x":210,"y":800,"wires":[["a677ac13.f570e"]]},{"id":"a677ac13.f570e","type":"moment","z":"e77fb21.e5d235","name":"YYYY-MM-DD HH:mm:ss.SSSSSS","topic":"","input":"","inputType":"msg","inTz":"Asia/Hong_Kong","adjAmount":0,"adjType":"days","adjDir":"add","format":"YYYY-MM-DD HH:mm:ss.SSSSSS","locale":"zh_TW","output":"payload","outputType":"msg","outTz":"Asia/Hong_Kong","x":440,"y":800,"wires":[["4f431c1c.510e54"]]},{"id":"4f431c1c.510e54","type":"debug","z":"e77fb21.e5d235","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":680,"y":800,"wires":},{"id":"b5d9b59b.c78b28","type":"serial in","z":"e77fb21.e5d235","name":"","serial":"e2641fb4.38abf","x":90,"y":280,"wires":[["32b9d353.b10cac"]]},{"id":"32b9d353.b10cac","type":"function","z":"e77fb21.e5d235","name":"Add Date","func":"//var date;\n//date = new Date();\n//date = (date.getFullYear() + '-' + ('00' + (date.getMonth()+1)).slice(-2) + '-' + ('00' + date.getDate()).slice(-2) + ' ' + ('00' + date.getHours()).slice(-2) + ':' + ('00' + date.getMinutes()).slice(-2) + ':' + ('00' + date.getSeconds()).slice(-2));\nvar date = global.get("date3");\n\nvar newMsg = msg;\nnewMsg.payload = newMsg.payload.replace ('\n', '');\nnewMsg.payload = "("+newMsg.payload + "," + "'"+ date + "'" +")";\nreturn newMsg;","outputs":1,"noerr":0,"x":240,"y":280,"wires":[["7c1d5cd6.1807d4"]]},{"id":"7c1d5cd6.1807d4","type":"join","z":"e77fb21.e5d235","name":"","mode":"custom","build":"string","property":"payload","propertyType":"msg","key":"topic","joiner":",","joinerType":"str","accumulate":false,"timeout":"","count":"200","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":390,"y":280,"wires":[["f7e4e357.712b1"]]},{"id":"f7e4e357.712b1","type":"function","z":"e77fb21.e5d235","name":"Topic","func":"var newMsg = msg\nnewMsg.topic = "INSERT INTO liftcar_table (Time_Stamp, AcX, AcY, AcZ, AcX1, AcY1, AcZ1, IR, RealTime) VALUES " + msg.payload + ";";\nnewMsg.payload ="";\nreturn newMsg;","outputs":1,"noerr":0,"x":530,"y":280,"wires":[["505be3b7.f3d7fc","a6c77ed0.37fe4"]]},{"id":"505be3b7.f3d7fc","type":"mysql","z":"e77fb21.e5d235","mydb":"7850ebd4.b86ad4","name":"MYSQL_liftcar","x":700,"y":280,"wires":[["7f9c3bb0.c734f4"]]},{"id":"7f9c3bb0.c734f4","type":"debug","z":"e77fb21.e5d235","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":890,"y":280,"wires":},{"id":"a6c77ed0.37fe4","type":"debug","z":"e77fb21.e5d235","name":"Frame from Sensors","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":720,"y":340,"wires":},{"id":"cf9bc84f.b42d08","type":"moment","z":"e77fb21.e5d235","name":"YYYY-MM-DD HH:mm:ss.SSSSSS","topic":"Set to Global Value","input":"","inputType":"msg","inTz":"Asia/Hong_Kong","adjAmount":0,"adjType":"days","adjDir":"add","format":"YYYY-MM-DD HH:mm:ss.SSSSSS","locale":"zh_TW","output":"date3","outputType":"global","outTz":"Asia/Hong_Kong","x":480,"y":140,"wires":[]},{"id":"311d4bc1.3d0324","type":"inject","z":"e77fb21.e5d235","name":"","topic":"","payload":"","payloadType":"date","repeat":"0.001","crontab":"","once":false,"onceDelay":0.1,"x":250,"y":140,"wires":[["cf9bc84f.b42d08"]]},{"id":"e2641fb4.38abf","type":"serial-port","z":"","serialport":"COM4","serialbaud":"115200","databits":"8","parity":"none","stopbits":"1","waitfor":"","newline":"\n","bin":"false","out":"char","addchar":"","responsetimeout":"10000"},{"id":"7850ebd4.b86ad4","type":"MySQLdatabase","z":"","host":"127.0.0.1","port":"3306","db":"lift_database","tz":""}]
Export your flow again, edit your post and replace the current code, this time format it like:
```
your flow code
```
Do you know how to put this kind of date Time ms (2019-09-01 02:26:34.779242) into msg.payload? Thx
take a look at node-red-contrib-moment
Hi, I did use the moment. Input is 0.001s timestamp and output to a global variable. But the output time sometimes gives same value even through I call it every 0.005s. That's why I asked if I can put the Date with ms format into my code instead of going through this work-around process. Pls see my previous code for details. Thx
Change your code to something that can be used by the people here on the forum, as indicated above.
If you send it to a global variable then remember that the value will change every time you write to it, so may have changed by the time you get round to using it. Don't use a global variable, pass it in a message and add it into the sensor message then pass it on to MYSQL. Don't use global variables unless there is a really good reason.
Hi, but how to get the time in ms (like Date() but it has no ms >_<) in the code instead of using node? Thx
「Colin via Node-RED Forum nodered@discoursemail.com」在 2019年9月2日 週一，上午12:07 寫道：
Construct a message with your data in message properties and put Date.now() in the payload. Pass that to the moment node and get it to put the formatted date in the payload, then modify the message to put the date in the appropriate place. The moment node will not change message properties that is not interested in.
It is working now. :> Thx for all the help.
var date;
date = new Date();
date = (date.getFullYear() + '-' + ('00' + (date.getMonth()+1)).slice(-2) + '-' + ('00' + date.getDate()).slice(-2) + ' ' + ('00' + date.getHours()).slice(-2) + ':' + ('00' + date.getMinutes()).slice(-2) + ':' + ('00' + date.getSeconds()).slice(-2) + '.' + ('00' + date.getMilliseconds()).slice(-6));
I haven't had time to work through this whole thread but are you using a prepared statement for your inserts? That should be a LOT faster if you aren't.
Also, I would think that batch inserts would be a lot more efficient?
2 Likes
If you are calling MySQL via a name (not by IP), put that name in your /etc/hosts to avoid DNS lookups or consider using an IP (not name) and turning off dns lookups like http://slaptijack.com/software/mysql-disable-dns-lookups/
1 Like
