SOLVED ... Sqlite insert 2 readings on the same row?

General
1

Hi,

I am trying to insert 2 readings on the same row in SQLITE.

I have looked on the form but cannot find the answer ?
Below is my function that receives 2 payloads Date and Time.
When inserted into Sqlite the Date is on one row and the Time on the next row ?

Where am i going wrong ?

var a = msg.date;
var b = msg.time;

msg.topic = "INSERT INTO Timestamp (Date,Time) VALUES (?,?)";
msg.payload = [a,b];
return msg;

Thanks

2

Are you sure? Has the Timestamp table got two columns? Though I would have expected an error if it were that.
Put a debug node on the output of the sqlite node and set it to show Full Message. Then inject your query. Expand the debug to show everything returned and post an image here.
Run the command line sqlite client and execute
select * from Timestamp;
and post the result here. Alternatively run that query in node-red and post the result.

3

Hi Colin,

This is from debug node on the output of the sqlite node

11/1/2018, 4:37:00 PMnode: 9a51449b.e8423INSERT INTO Timestamp (Date,Time) VALUES (?,?) : msg.payload : array[2]
[ "01 11 18", null ]
11/1/2018, 4:37:00 PMnode: 9a51449b.e8423INSERT INTO Timestamp (Date,Time) VALUES (?,?) : msg.payload : array[2]
[ null, "04 40" ]
11/1/2018, 4:37:00 PMnode: 2b4f2d42.1654b2INSERT INTO Timestamp (Date,Time) VALUES (?,?) : msg.payload : array[0]
[ empty ]
11/1/2018, 4:37:00 PMnode: 2b4f2d42.1654b2INSERT INTO Timestamp (Date,Time) VALUES (?,?) : msg.payload : array[0]
[ empty ]

And my database
42

4

Just do one insert please. Also put a debug on the input to the sqlite node, I suspect that you have not got both values in the array. Also you haven't put the output debug node into Complete Message mode as I asked.
I think the debug o/p is easier to read if you post a screenshot.

5

Hi Colin,

I am new to debugging on Node-Red and i do not know how to get into Complete Message mode.

Here is a screenshot of the debug output from the input to DB and the Output of it 57

6

Here is my flow

[{"id":"7b42b306.457044","type":"sqlite","z":"a13b9737.d91218","mydb":"ce0f7aff.edb42","name":"HA Database","x":730,"y":820,"wires":[["2b4f2d42.1654b2"]]},{"id":"8ae89164.01cb8","type":"inject","z":"a13b9737.d91218","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"x":100,"y":820,"wires":[["c28bd594.24a598","a9f46a1.ba08818"]]},{"id":"c28bd594.24a598","type":"moment","z":"a13b9737.d91218","name":"Timestamp to DD MM YY","topic":"","input":"","inputType":"msg","inTz":"Europe/London","adjAmount":0,"adjType":"days","adjDir":"add","format":"DD MM YY","locale":"en_GB","output":"date","outputType":"msg","outTz":"Europe/London","x":310,"y":820,"wires":[["91b08c2d.b8c8d"]]},{"id":"a9f46a1.ba08818","type":"moment","z":"a13b9737.d91218","name":"Timestamp to Time","topic":"","input":"","inputType":"msg","inTz":"Europe/London","adjAmount":0,"adjType":"days","adjDir":"add","format":"hh mm","locale":"en_GB","output":"time","outputType":"msg","outTz":"Europe/London","x":290,"y":860,"wires":[["91b08c2d.b8c8d"]]},{"id":"91b08c2d.b8c8d","type":"function","z":"a13b9737.d91218","name":"INSERT into Date","func":"\nvar a = msg.date;\nvar b = msg.time;\n\nmsg.topic = "INSERT INTO Timestamp (Date,Time) VALUES (?,?)";\nmsg.payload = [a,b];\nreturn msg;\n\n","outputs":1,"noerr":0,"x":550,"y":860,"wires":[["7b42b306.457044","9a51449b.e8423"]]},{"id":"9a51449b.e8423","type":"debug","z":"a13b9737.d91218","name":"","active":true,"console":"false","complete":"payload","x":730,"y":860,"wires":},{"id":"2b4f2d42.1654b2","type":"debug","z":"a13b9737.d91218","name":"","active":true,"console":"true","complete":"payload","x":900,"y":820,"wires":},{"id":"ce0f7aff.edb42","type":"sqlitedb","z":"","db":"/home/gb/Samba_shared_folder/HA_Database_V1_00.db"}]

06

7

When you edit the Debug node hit the drop down arrow and select "complete msg object"
Screenshot_2018-11-01_17-07-08

8

Thanks ghayne, always learning on Nod-red :grin:

Here is full outputs

11

9

Assume that the first line from the debug you posted is the input to the node
[ "01 11 18", null]
you can see that only the date is there, which is why it only inserts the date. So the problem is not with the sqlite node but the fact that you only have half the data.

10

You also have two nodes feeding the insert function node, which means two inserts, try to use one node before the insert node.

11

Hi ghayne

I am not sure how to do that, this was the only way I could see how to get separate date and time ?

I am using the moment node

12

I cannot access node red at the moment, I'm sure someone will answer soon!

13

Ok thanks :grin:

14

One idea: put a delay node before the second moment node(Timestamp to Time) with a small (0.1s) delay and a join node after both moment nodes combining both values into an array. The delay ensures you have the values in the correct order.

15

Ok thanks, I will look at that later, time for tea :blush:

16

Thanks Colin and ghayne,

I managed to get it working correctly :grinning: I had to use the JOIN node.

The flow image ...
24

And the actual flow here (for anyone that reads this in the future) ...

[{"id":"7b42b306.457044","type":"sqlite","z":"a13b9737.d91218","mydb":"ce0f7aff.edb42","name":"HA Database","x":870,"y":820,"wires":[["9a51449b.e8423"]]},{"id":"8ae89164.01cb8","type":"inject","z":"a13b9737.d91218","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"x":140,"y":820,"wires":[["c28bd594.24a598","a9f46a1.ba08818"]]},{"id":"c28bd594.24a598","type":"moment","z":"a13b9737.d91218","name":"Timestamp to DD MM YY","topic":"","input":"","inputType":"msg","inTz":"Europe/London","adjAmount":0,"adjType":"days","adjDir":"add","format":"DD MM YY","locale":"en_GB","output":"payload","outputType":"msg","outTz":"Europe/London","x":350,"y":820,"wires":[["e167ab0c.dd1698"]]},{"id":"a9f46a1.ba08818","type":"moment","z":"a13b9737.d91218","name":"Timestamp to Time","topic":"","input":"","inputType":"msg","inTz":"Europe/London","adjAmount":0,"adjType":"days","adjDir":"add","format":"hh mm","locale":"en_GB","output":"payload","outputType":"msg","outTz":"Europe/London","x":370,"y":860,"wires":[["e167ab0c.dd1698"]]},{"id":"91b08c2d.b8c8d","type":"function","z":"a13b9737.d91218","name":"INSERT","func":"\nvar a = msg.payload [0]\nvar b = msg.payload [1]\n\n\nmsg.topic = "INSERT INTO Timestamp (Date,Time) VALUES (?,?)";\nmsg.payload = [a,b];\nreturn msg;\n\n","outputs":1,"noerr":0,"x":700,"y":820,"wires":[["7b42b306.457044"]]},{"id":"9a51449b.e8423","type":"debug","z":"a13b9737.d91218","name":"","active":true,"console":"false","complete":"true","x":1010,"y":820,"wires":},{"id":"2b4f2d42.1654b2","type":"debug","z":"a13b9737.d91218","name":"","active":true,"console":"false","complete":"true","x":690,"y":860,"wires":},{"id":"e167ab0c.dd1698","type":"join","z":"a13b9737.d91218","name":"","mode":"custom","build":"array","property":"payload","propertyType":"msg","key":"topic","joiner":"\n","timeout":"","count":"2","x":550,"y":820,"wires":[["91b08c2d.b8c8d","2b4f2d42.1654b2"]]},{"id":"ce0f7aff.edb42","type":"sqlitedb","z":"","db":"/home/gb/Samba_shared_folder/HA_Database_V1_00.db"}]

17

Are you sure you want to save the date and time separately in the database? It would be more usual to put the timestamp in an Integer column and save the Unix timestamp there (so that is the javascript time/1000). The sqlite db has query methods that allow you to select by date or range and so on.

18

Hi Colin,

I am still learning about databases, but I will save the Unix timestamp as well.
Yesterday was mostly about getting data into the correct rows.

Now I can progress to more useful ways to use the database, Storing gas and electricity usage. and ways of reducing the amount used.

Thanks for the info on query methods, I will be looking further into this in the coming days.

Thanks

19

Generally it is a bad idea to store the same data in two different ways at the same time. There is no point (except in rare situations where it may be necessary for efficiency reasons) and adds complication, as you have already found out.
Also watch out for the dangers of storing time in local time if that is what you are doing. If you are in a region where the clocks change summer/winter then there will a period when the clocks go back that you cannot tell from the local time which side of the divide you are. By storing the Unix time (which is UTC) and converting when you want to display the data then you avoid this problem.

20

Hi Colin,

Thanks for that, I will save UTC time into database.