Dear @zenofmud thank you for your helps.
But after I added manually time and date in DB, it gives an error and dont write to db. how can I add date and time this code ?
Read the error message. What does it tell you?
I added these table later, but I dont know how can I describe it.
What does the error say?
table cube has 5 columns but 4 values were supplied
You have 5 columns in your table, but your insert statement is only providing 4 values.
I understand problem but I dont know how can I solve this.
I also want to added counter how many log we have. How can I descibe this ?
You have a couple options:
- change the sql statement to specify the column names you are adding
- add another value to the value clause for the 5th element
- delete one column from the sql table.
At this point, you might find it very helpful to take a tutorial about SQL like this one:
and here is onw covering SQL in general:
https://www.w3schools.com/sql/
These should give you have a better feel of how to solve issues like this.
Which one is better and faster for me, just want to do this. How can I add time in this code.
At this point, Paul and I have spent a lot of time coaching you through this, trying to help you learn by doing. I'm sorry if the lack of an instant answer is frustrating you - but please remember we aren't paid to spend our time writing code for you.
You need to:
- decide exactly what columns you want in the table
- change your Insert statement to provide data to each of those columns
You've been given all the tools you need to achieve this.
1 Like
I 'm greatfull for your helps but this is only area which people take can help, and need help.
I also spending lots of time here and back of node-red.
I added this code and now need to correct date and time format. In phpmyadmin date and time format is not clear.
var date = Date.now();
var d = new Date();
var current = d.getTime();
var newMsg = {
"topic": "INSERT INTO cube VALUES ( "+ date +" ,"+ current +" , \"" +
msg.topic + "\",\"" +
msg.label + "\"," + msg.payload + ")"
}
return newMsg;
Here's a link to documentation about what Date.now() returns - https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Date/now
That explains the format.
If you want a different format, look at the other methods available on the Date object - https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Date#
I dont understand anything this kind of documents or sources. If I understand, I cant ask here
In that case, as has been suggested on numerous occasions you should either hire an engineer with some javascript experience or work through some javascript tutorials yourself.
Thank you for your advice, I just want to solve my problem not more. I changed this code but stil looks not clear date and time.
var date = Date.now();
setTimeout(function() {
var millis = Date.now() - date;
console.log("seconds elapsed = " + Math.floor(millis/1000));
}, 2000);
var newMsg = {
"topic": "INSERT INTO cube VALUES ( "+ date +" , \"" +
msg.topic + "\",\"" +
msg.label + "\"," + msg.payload + ")"
}
return newMsg;
From those links:
The
Date.now()method returns the number of milliseconds elapsed since January 1, 1970 00:00:00 UTC.
It tells you exactly why the date and time values you are inserting into the database are the long numbers you are seeing. I really cannot make it any more clearer for you.
Tell us what you want to insert into the database. You have a 'time' and 'date' column. What do you want the data in those columns to look like? Give us an example of what your perfect entry will look like. Without that, all we can do is point at the documentation for the Date object and leave you to read about the various methods available to you.
Only date and time I want to insert DB because, this is needed for logging. I put date and time column in database, is that wrong ?
SO basically I want this kind of logging in DB
Date Status Name Value
14.11.2018 16:07:00 Stopped Laser 1 Error 1
14.11.2018 16:15:33 Running Laser 1 Working 0
14.11.2018 17:24:13 Stopped Laser 1 Error 1
14.11.2018 17:35:41 Running Laser 1 Working 0
Normally you would have a single column for the date/time - there's little reason to split it into two separate columns as you have done.
Using Date.now() gives you a convenient way to store the date.... but it isn't very readable, but that wouldn't matter depending on what you plan to do with this data later on.
I would suggest:
- replace your two separate columns with just one 'date' column with a type of
text - change your INSERT statement to insert the string
'CURRENT_TIMESTAMP'rather than worry about using the Date object. That is a special string that will cause sqlite to insert the current timestamp value into the column for you.
Dear @knolleary I created new db and make data and time only 1 column, then I used CURRENT_TIMESTAMP for date, but still same.
var CURRENT_TIMESTAMP = Date.now();
var newMsg = {
"topic": "INSERT INTO Test VALUES ( "+ CURRENT_TIMESTAMP +" ,\"" +
msg.topic + "\",\"" +
msg.label + "\"," + msg.payload + ")"
}
return newMsg;
You have misunderstood what I suggested. I said to insert the string CURRENT_TIMESTAMP - instead, youv'e created a local javascript variable called CURRENT_TIMESTAMP, assigned it the value of Date.now() and insert that.
Your Insert statement should start with:
INSERT INTO Test VALUES ( 'CURRENT_TIMESTAMP', ....Thanks for all of your helps and spent times. I'm gratefull.After your suggestion, its ok. If someone need, I put the basic flow.
After @knolleary advice CURRENT_TIMESTAMP, .......... its ok.
[{"id":"1b8c0072.f56e","type":"rpi-gpio in","z":"b7ef5111.6c069","name":"","pin":"40","intype":"tri","debounce":"25","read":false,"x":179,"y":185,"wires":[["c0c84a4d.4654a8"]]},{"id":"c0c84a4d.4654a8","type":"dsm","z":"b7ef5111.6c069","name":"state right","sm_config":"{\n \"triggerInput\": \"payload\",\n \"currentState\": \"running\",\n \"states\": {\n \"stopped\": {\n \"0\": \"running\"\n },\n \"running\": {\n \"1\": \"stopped\"\n }\n },\n \"methods\": {\n \"onBeforeTransition\": \"msg.pyload = msg.payload.toString();\",\n \"0\": \"sta.fill='green';msg.background='green';msg.label='Laser 1 Working';\",\n \"1\": \"sta.fill='red';msg.background='red';msg.label='Laser 1 Error!!!';\"\n }\n}","x":365,"y":183,"wires":[["f7f67a57.4b6928","548cc839.4f6c58","47215b47.e7d2e4"]]},{"id":"f7f67a57.4b6928","type":"ui_button","z":"b7ef5111.6c069","name":"Laser 1","group":"e09641cc.ff444","order":0,"width":0,"height":0,"passthru":false,"label":"{{msg.label}}","color":"","bgcolor":"{{msg.background}}","icon":"","payload":"","payloadType":"str","topic":"","x":529,"y":105,"wires":[[]]},{"id":"548cc839.4f6c58","type":"debug","z":"b7ef5111.6c069","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","x":519,"y":145,"wires":[]},{"id":"47215b47.e7d2e4","type":"function","z":"b7ef5111.6c069","name":"insert Statement","func":"var date = Date.now();\n\nvar newMsg = {\n \"topic\": \"INSERT INTO test VALUES (CURRENT_TIMESTAMP, \\\"\" +\n msg.topic + \"\\\",\\\"\" + \n msg.label + \"\\\",\" + msg.payload + \")\"\n}\n\nreturn newMsg;","outputs":1,"noerr":0,"x":569,"y":185,"wires":[["74f03903.f7e708"]]},{"id":"74f03903.f7e708","type":"sqlite","z":"b7ef5111.6c069","mydb":"2c43a6d2.a8fd6a","sqlquery":"msg.topic","sql":"","name":"Record","x":729,"y":185,"wires":[[]]},{"id":"5450c97.8f0b438","type":"inject","z":"b7ef5111.6c069","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":189,"y":145,"wires":[["c0c84a4d.4654a8"]]},{"id":"e09641cc.ff444","type":"ui_group","z":"","name":"Assembly Lines","tab":"816c52e6.4c569","disp":true,"width":"6","collapse":false},{"id":"2c43a6d2.a8fd6a","type":"sqlitedb","z":"","db":"/var/www/html/dbase/test","mode":"RWC"},{"id":"816c52e6.4c569","type":"ui_tab","z":"","name":"Chasis Lines","icon":"dashboard"}]
Sorry to revive this... I think it's because I'm definitely not a coder (PLC and Fanuc programmer so I understand logic and flow very well).
Trying to get data into my DB for storage, display, etc... here's my flow:
[{"id":"dd452be6.ad83a8","type":"mqtt in","z":"ff6f3b86.a45ac8","name":"Bin1Data","topic":"Bins/Bin1/LevelData","qos":"0","datatype":"auto","broker":"5d86e7b6.6ca968","x":340,"y":240,"wires":[["8af7d270.9a38","2b8140f9.3ba3e","3684e130.adc13e"]]},{"id":"8af7d270.9a38","type":"debug","z":"ff6f3b86.a45ac8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":true,"complete":"payload","targetType":"msg","statusVal":"payload","statusType":"auto","x":590,"y":120,"wires":[]},{"id":"2b8140f9.3ba3e","type":"debug","z":"ff6f3b86.a45ac8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":true,"complete":"topic","targetType":"msg","statusVal":"payload","statusType":"auto","x":580,"y":180,"wires":[]},{"id":"3684e130.adc13e","type":"function","z":"ff6f3b86.a45ac8","name":"","func":"var CURRENT_TIMESTAMP = Date.now();\n\n\nvar newMsg = {\n \"topic\": \"INSERT INTO readings VALUES ( 'CURRENT_TIMESTAMP' , \" + msg.payload + \")\"\n}\n\nreturn newMsg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":620,"y":360,"wires":[["f7ffbb8f.621ff8","15b93391.c3a7ac","6765c5af.17d09c"]]},{"id":"7ca254c3.d354dc","type":"sqlite","z":"ff6f3b86.a45ac8","mydb":"154d9696.b27e29","sqlquery":"msg.topic","sql":"","name":"DB","x":830,"y":360,"wires":[[]]},{"id":"29d3fac8.c10046","type":"mqtt in","z":"ff6f3b86.a45ac8","name":"Bin2Data","topic":"Bins/Bin2/LevelData","qos":"0","datatype":"auto","broker":"5d86e7b6.6ca968","x":340,"y":300,"wires":[["3684e130.adc13e"]]},{"id":"be3bc7d6.d64af8","type":"mqtt in","z":"ff6f3b86.a45ac8","name":"Bin3Data","topic":"Bins/Bin3/LevelData","qos":"0","datatype":"auto","broker":"5d86e7b6.6ca968","x":340,"y":360,"wires":[["3684e130.adc13e"]]},{"id":"754f55c5.d8f6bc","type":"mqtt in","z":"ff6f3b86.a45ac8","name":"Bin4Data","topic":"Bins/Bin4/LevelData","qos":"0","datatype":"auto","broker":"5d86e7b6.6ca968","x":340,"y":420,"wires":[["3684e130.adc13e"]]},{"id":"e481d060.05ce","type":"mqtt in","z":"ff6f3b86.a45ac8","name":"Bin5Data","topic":"Bins/Bin5/LevelData","qos":"0","datatype":"auto","broker":"5d86e7b6.6ca968","x":340,"y":480,"wires":[["3684e130.adc13e"]]},{"id":"bbbd2bf0.aefac8","type":"inject","z":"ff6f3b86.a45ac8","name":"Create","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"CREATE TABLE readings(id INTEGER PRIMARY KEY AUTOINCREMENT, timestamp,lcn TEXT, ss NUMERIC, bl NUMERIC)","payload":"","payloadType":"date","x":640,"y":300,"wires":[["7ca254c3.d354dc"]]},{"id":"f7ffbb8f.621ff8","type":"debug","z":"ff6f3b86.a45ac8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":true,"complete":"payload","targetType":"msg","statusVal":"payload","statusType":"auto","x":850,"y":440,"wires":[]},{"id":"15b93391.c3a7ac","type":"debug","z":"ff6f3b86.a45ac8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":true,"complete":"topic","targetType":"msg","statusVal":"topic","statusType":"auto","x":840,"y":500,"wires":[]},{"id":"6765c5af.17d09c","type":"debug","z":"ff6f3b86.a45ac8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":true,"complete":"true","targetType":"full","statusVal":"payload","statusType":"auto","x":830,"y":560,"wires":[]},{"id":"5d86e7b6.6ca968","type":"mqtt-broker","name":"Bins Broker","broker":"localhost","port":"1883","clientid":"","usetls":false,"compatmode":true,"keepalive":"60","cleansession":true,"birthTopic":"Bins/Server/Status/Comm","birthQos":"2","birthRetain":"true","birthPayload":"Connected","closeTopic":"Bins/Server/Status/Comm","closeQos":"2","closeRetain":"true","closePayload":"Disconnected","willTopic":"Bins/Server/Status/Comm","willQos":"2","willRetain":"true","willPayload":"Unexpected Disconnection"},{"id":"154d9696.b27e29","type":"sqlitedb","db":"/tmp/sqlite","mode":"RWC"}]
I (think) I can change the objects for 'ss' and 'bl' to numbers... I just don't know how to code my function to output the message into an insertable format to the DB...
Any help on this (especially my function) would be helpful... I didn't connect my DB insert to my SQL node intentionally until because I haven't created the DB yet... I'd like to get the msg.payload correct first!
The MQTT messages in come from 5 remote PI 3 b+s with sensors to a main PI 4 b for control, display and user access.
Thank you in advance!
