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

**URL:** <https://discourse.nodered.org/t/solved-sqlite-insert-2-readings-on-the-same-row/4480>\
**Category:** General\
**Created:** [1 November 2018 15:51 UTC](https://discourse.nodered.org/t/solved-sqlite-insert-2-readings-on-the-same-row/4480 "2018-11-01T15:51:26Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![questuk](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/questuk/32/4059_2.png) [@questuk](https://discourse.nodered.org/u/questuk)\
**Post date:** [1 November 2018 15:51 UTC](https://discourse.nodered.org/t/solved-sqlite-insert-2-readings-on-the-same-row/4480/1 "2018-11-01T15:51:26Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [1 November 2018 16:15 UTC](https://discourse.nodered.org/t/solved-sqlite-insert-2-readings-on-the-same-row/4480/2 "2018-11-01T16:15:39Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![questuk](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/questuk/32/4059_2.png) [@questuk](https://discourse.nodered.org/u/questuk)\
**Post date:** [1 November 2018 16:45 UTC](https://discourse.nodered.org/t/solved-sqlite-insert-2-readings-on-the-same-row/4480/3 "2018-11-01T16:45:02Z")

</div>

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](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/5/52056954fc0822fe006e48520fa02d1e98700527.jpeg)

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [1 November 2018 16:49 UTC](https://discourse.nodered.org/t/solved-sqlite-insert-2-readings-on-the-same-row/4480/4 "2018-11-01T16:49:49Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![questuk](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/questuk/32/4059_2.png) [@questuk](https://discourse.nodered.org/u/questuk)\
**Post date:** [1 November 2018 17:00 UTC](https://discourse.nodered.org/t/solved-sqlite-insert-2-readings-on-the-same-row/4480/5 "2018-11-01T17:00:31Z")

</div>

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](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/1/12960ae1f7ffb189f064eea07f5e1b08f199dee4.jpeg)

---

<div class="post-metadata">

**Author:** ![questuk](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/questuk/32/4059_2.png) [@questuk](https://discourse.nodered.org/u/questuk)\
**Post date:** [1 November 2018 17:05 UTC](https://discourse.nodered.org/t/solved-sqlite-insert-2-readings-on-the-same-row/4480/6 "2018-11-01T17:05:21Z")

</div>

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](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/e/e8e8a29c51a3f8cc5d7f81d0413ddb048d30d736.jpeg)

---

<div class="post-metadata">

**Author:** ![ghayne](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/ghayne/32/39_2.png) [@ghayne](https://discourse.nodered.org/u/ghayne)\
**Post date:** [1 November 2018 17:10 UTC](https://discourse.nodered.org/t/solved-sqlite-insert-2-readings-on-the-same-row/4480/7 "2018-11-01T17:10:41Z")

</div>

> [@questuk](#):
>
> I am new to debugging on Node-Red and i do not know how to get into Complete Message mode.

When you edit the Debug node hit the drop down arrow and select "complete msg object"  
 ![Screenshot_2018-11-01_17-07-08](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/5/5f810ebdbffeb1e5dc447ee769fe359254cc0dcd.png)

---

<div class="post-metadata">

**Author:** ![questuk](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/questuk/32/4059_2.png) [@questuk](https://discourse.nodered.org/u/questuk)\
**Post date:** [1 November 2018 17:13 UTC](https://discourse.nodered.org/t/solved-sqlite-insert-2-readings-on-the-same-row/4480/8 "2018-11-01T17:13:49Z")

</div>

Thanks ghayne, always learning on Nod-red 😁

Here is full outputs

![11](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/4/4ee51676b7de4e407b700a929cc616f2bf32e7ad.jpeg)

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [1 November 2018 17:17 UTC](https://discourse.nodered.org/t/solved-sqlite-insert-2-readings-on-the-same-row/4480/9 "2018-11-01T17:17:55Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![ghayne](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/ghayne/32/39_2.png) [@ghayne](https://discourse.nodered.org/u/ghayne)\
**Post date:** [1 November 2018 17:20 UTC](https://discourse.nodered.org/t/solved-sqlite-insert-2-readings-on-the-same-row/4480/10 "2018-11-01T17:20:51Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![questuk](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/questuk/32/4059_2.png) [@questuk](https://discourse.nodered.org/u/questuk)\
**Post date:** [1 November 2018 17:25 UTC](https://discourse.nodered.org/t/solved-sqlite-insert-2-readings-on-the-same-row/4480/11 "2018-11-01T17:25:31Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![ghayne](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/ghayne/32/39_2.png) [@ghayne](https://discourse.nodered.org/u/ghayne)\
**Post date:** [1 November 2018 17:32 UTC](https://discourse.nodered.org/t/solved-sqlite-insert-2-readings-on-the-same-row/4480/12 "2018-11-01T17:32:17Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![questuk](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/questuk/32/4059_2.png) [@questuk](https://discourse.nodered.org/u/questuk)\
**Post date:** [1 November 2018 17:32 UTC](https://discourse.nodered.org/t/solved-sqlite-insert-2-readings-on-the-same-row/4480/13 "2018-11-01T17:32:40Z")

</div>

Ok thanks 😁

---

<div class="post-metadata">

**Author:** ![ghayne](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/ghayne/32/39_2.png) [@ghayne](https://discourse.nodered.org/u/ghayne)\
**Post date:** [1 November 2018 17:39 UTC](https://discourse.nodered.org/t/solved-sqlite-insert-2-readings-on-the-same-row/4480/14 "2018-11-01T17:39:21Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![questuk](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/questuk/32/4059_2.png) [@questuk](https://discourse.nodered.org/u/questuk)\
**Post date:** [1 November 2018 17:42 UTC](https://discourse.nodered.org/t/solved-sqlite-insert-2-readings-on-the-same-row/4480/15 "2018-11-01T17:42:18Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![questuk](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/questuk/32/4059_2.png) [@questuk](https://discourse.nodered.org/u/questuk)\
**Post date:** [1 November 2018 19:29 UTC](https://discourse.nodered.org/t/solved-sqlite-insert-2-readings-on-the-same-row/4480/16 "2018-11-01T19:29:53Z")

</div>

Thanks Colin and ghayne,

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

The flow image ...  
 ![24](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/8/81ad1746145f503e9c726c3a2deb18741d6b2964.jpeg)

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"}]

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [1 November 2018 20:12 UTC](https://discourse.nodered.org/t/solved-sqlite-insert-2-readings-on-the-same-row/4480/17 "2018-11-01T20:12:38Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![questuk](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/questuk/32/4059_2.png) [@questuk](https://discourse.nodered.org/u/questuk)\
**Post date:** [2 November 2018 07:47 UTC](https://discourse.nodered.org/t/solved-sqlite-insert-2-readings-on-the-same-row/4480/18 "2018-11-02T07:47:53Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [2 November 2018 09:10 UTC](https://discourse.nodered.org/t/solved-sqlite-insert-2-readings-on-the-same-row/4480/19 "2018-11-02T09:10:37Z")

</div>

> [@questuk](#):
>
> I am still learning about databases, but I will save the Unix timestamp as well

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.

---

<div class="post-metadata">

**Author:** ![questuk](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/questuk/32/4059_2.png) [@questuk](https://discourse.nodered.org/u/questuk)\
**Post date:** [2 November 2018 09:15 UTC](https://discourse.nodered.org/t/solved-sqlite-insert-2-readings-on-the-same-row/4480/20 "2018-11-02T09:15:29Z")

</div>

Hi Colin,

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