# Update query in sqlite3 with fob id coming from gateway code

**URL:** <https://discourse.nodered.org/t/update-query-in-sqlite3-with-fob-id-coming-from-gateway-code/32292>\
**Category:** General\
**Created:** [1 September 2020 10:08 UTC](https://discourse.nodered.org/t/update-query-in-sqlite3-with-fob-id-coming-from-gateway-code/32292 "2020-09-01T10:08:49Z")\
**Posts on this page:** 16\
**Page:** 7

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [8 September 2020 12:02 UTC](https://discourse.nodered.org/t/update-query-in-sqlite3-with-fob-id-coming-from-gateway-code/32292/121 "2020-09-08T12:02:21Z")

</div>

Some where you are building the sql statement. Look at that code and see why the value you are using for the ID is not valid.

---

<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:** [8 September 2020 12:34 UTC](https://discourse.nodered.org/t/update-query-in-sqlite3-with-fob-id-coming-from-gateway-code/32292/122 "2020-09-08T12:34:38Z")

</div>

> [@shipra](#):
>
> I am using moment node which is giving me correct date but i want UK tine as well as India time.

You might think that is what you want, but really it isn't. You should insert the current time in the database using the technique already discussed in great detail, using a column that will automatically put the time in, then if you want to know the time in any particular timezone you can pick up the time from the database (which will be UTC) and convert it to the appropriate timezone for display.

---

<div class="post-metadata">

**Author:** ![shipra](https://avatars.discourse-cdn.com/v4/letter/s/71c47a/32.png) [@shipra](https://discourse.nodered.org/u/shipra)\
**Post date:** [8 September 2020 14:04 UTC](https://discourse.nodered.org/t/update-query-in-sqlite3-with-fob-id-coming-from-gateway-code/32292/123 "2020-09-08T14:04:22Z")

</div>

Sir i have follwed this link ,i got this as output

```auto
sqlite> SELECT * FROM NCS_DATABASE1;
1|2020-09-08T19:32:39.922Z| | |04222436| |2814| | |

```

---

<div class="post-metadata">

**Author:** ![shipra](https://avatars.discourse-cdn.com/v4/letter/s/71c47a/32.png) [@shipra](https://discourse.nodered.org/u/shipra)\
**Post date:** [8 September 2020 14:05 UTC](https://discourse.nodered.org/t/update-query-in-sqlite3-with-fob-id-coming-from-gateway-code/32292/124 "2020-09-08T14:05:31Z")

</div>

but what is command to insert timestamp in node red ,i am not getting that.please help

---

<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:** [8 September 2020 14:13 UTC](https://discourse.nodered.org/t/update-query-in-sqlite3-with-fob-id-coming-from-gateway-code/32292/125 "2020-09-08T14:13:59Z")

</div>

> [@shipra](#):
>
> what is command to insert timestamp in node red ,i

As I said we addressed that earlier. Don't insert a timestamp, get the database to insert it automatically.

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [8 September 2020 15:40 UTC](https://discourse.nodered.org/t/update-query-in-sqlite3-with-fob-id-coming-from-gateway-code/32292/126 "2020-09-08T15:40:09Z")

</div>

> [@shipra](#):
>
> but what is command to insert timestamp in node red ,i am not getting that.please help

You can get a timestamp from a `function`, `change` or `inject` node, not to menion some 'contrib' nodes. In a `function` node use the javascript `date()` function - if you don't know about the date() function google 'javascript data()' and read about it.

---

<div class="post-metadata">

**Author:** ![shipra](https://avatars.discourse-cdn.com/v4/letter/s/71c47a/32.png) [@shipra](https://discourse.nodered.org/u/shipra)\
**Post date:** [9 September 2020 05:30 UTC](https://discourse.nodered.org/t/update-query-in-sqlite3-with-fob-id-coming-from-gateway-code/32292/127 "2020-09-09T05:30:52Z")

</div>

Hello Sir Good Morning,I have tried it

```auto
let newMsg1 = {};
newMsg1.topic = `INSERT INTO NCS_DATABASE1 (Date_and_Time TEXT,Event_Type TEXT NOT NULL,Event_Description TEXT NOT NULL,Person_ID TEXT NOT NULL,Person_Name_Description TEXT NOT NULL,Fob_Tag_ID TEXT NOT NULL,Door_ID TEXT NOT NULL,Door_Description TEXT NOT NULL,Status TEXT NOT NULL)
VALUES (date('now'),' ',' ',' ','${msg.id}','Shipra Nigam','2814',' ',' ','DR1 ON')`;
return newMsg1;

```

but sir error is coming

error:

```auto
"Error: SQLITE_ERROR: near "TEXT": syntax error"

```

please suggest.

---

<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:** [9 September 2020 06:28 UTC](https://discourse.nodered.org/t/update-query-in-sqlite3-with-fob-id-coming-from-gateway-code/32292/128 "2020-09-09T06:28:45Z")

</div>

Have you worked through a good sqlite tutorial as was suggested months ago? If so then where in there did it suggest that an insert statement should look like that?

---

<div class="post-metadata">

**Author:** ![shipra](https://avatars.discourse-cdn.com/v4/letter/s/71c47a/32.png) [@shipra](https://discourse.nodered.org/u/shipra)\
**Post date:** [9 September 2020 07:54 UTC](https://discourse.nodered.org/t/update-query-in-sqlite3-with-fob-id-coming-from-gateway-code/32292/129 "2020-09-09T07:54:39Z")

</div>

> [@zenofmud](#):
>
> javascript data()

yes sir i checked my mistake now i have written correctly and output is coming

```auto
2|2020-09-09| | |04222436|Shipra Nigam|2814| | |DR1 ON

```

command

```auto
let newMsg1 = {};
newMsg1.topic = `INSERT INTO NCS_DATABASE1 (SrNo,Date_and_Time ,Event_Type ,Event_Description ,Person_ID,Person_Name_Description ,Fob_Tag_ID ,Door_ID ,Door_Description ,Status)
VALUES (2,date('now'),' ',' ','${msg.id}','Shipra Nigam','2814',' ',' ','DR1 ON')`;
return newMsg1;

```

But i want time too and i tried this in function but error is coming

```auto
let newMsg1 = {};
newMsg1.topic = `INSERT INTO NCS_DATABASE1 (SrNo,Date_and_Time ,Event_Type ,Event_Description ,Person_ID,Person_Name_Description ,Fob_Tag_ID ,Door_ID ,Door_Description ,Status)
VALUES (2,datetime('now','+5.5 hours'),' ',' ','${msg.id}','Shipra Nigam','2814',' ',' ','DR1 ON')`;
return newMsg1;

```

please suggest

---

<div class="post-metadata">

**Author:** ![shipra](https://avatars.discourse-cdn.com/v4/letter/s/71c47a/32.png) [@shipra](https://discourse.nodered.org/u/shipra)\
**Post date:** [9 September 2020 08:19 UTC](https://discourse.nodered.org/t/update-query-in-sqlite3-with-fob-id-coming-from-gateway-code/32292/130 "2020-09-09T08:19:23Z")

</div>

Sir i have done successfully with time also

```auto
sqlite> SELECT * FROM NCS_DATABASE1;
1| | | |04222436| |2814| | |DR1 ON
7|2020-09-09 13:47:20| | |04222436|Shipra Nigam|2814| | |DR1 ON

```

one issue is there i want uk time also to be mention in the same date and time column

---

<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:** [9 September 2020 08:39 UTC](https://discourse.nodered.org/t/update-query-in-sqlite3-with-fob-id-coming-from-gateway-code/32292/131 "2020-09-09T08:39:31Z")

</div>

How many times do we have to say it? Put the time in as a UTC timestamp (either manually if you must, but preferably letting the database do it automatically) and if you want it in a particular time zone then do that when you display it. Remember 12:00 UTC is _the same time_ as 13:00 UTC+1 and 17:00 UTC+5. There is only one time for Now, just different ways of displaying it.

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [9 September 2020 09:17 UTC](https://discourse.nodered.org/t/update-query-in-sqlite3-with-fob-id-coming-from-gateway-code/32292/132 "2020-09-09T09:17:39Z")

</div>

@shipra - if you want help answer ALL the following questions

1. do you understand what UTC implies? (Y/N)

- if not, google `UTC` and read about it

1. do you understand the different ways date/time can be stored in an sqlite database? (Y/N)

- if not google 'how to save timestamp in sqlite'

1. do you understand that a timestamp, when read from a database, can be displayed in the local time of any timezone in the world?

- if not, go back and reread about UTC

1. do you now understand why you only need the timestamp stored once? (Y/N)

- if not, go back and do some more homework and read about it again and take some online tutorials

If you answered YES to all these questions you should understand what you need to do and should be able to successfully finish your flow.

---

<div class="post-metadata">

**Author:** ![shipra](https://avatars.discourse-cdn.com/v4/letter/s/71c47a/32.png) [@shipra](https://discourse.nodered.org/u/shipra)\
**Post date:** [9 September 2020 15:06 UTC](https://discourse.nodered.org/t/update-query-in-sqlite3-with-fob-id-coming-from-gateway-code/32292/133 "2020-09-09T15:06:45Z")

</div>

sir ,now output is coming  
thank you so much for your guidance.

```auto
68|2020-09-09 16:00:04| | |2814|Shipra Nigam|2814| | |DR1 ON

```

---

<div class="post-metadata">

**Author:** ![shipra](https://avatars.discourse-cdn.com/v4/letter/s/71c47a/32.png) [@shipra](https://discourse.nodered.org/u/shipra)\
**Post date:** [9 September 2020 15:07 UTC](https://discourse.nodered.org/t/update-query-in-sqlite3-with-fob-id-coming-from-gateway-code/32292/134 "2020-09-09T15:07:18Z")

</div>

Sir thank you for guidance.now out out is coming

```auto
68|2020-09-09 16:00:04| | |2814|Shipra Nigam|2814| | |DR1 ON

```

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [9 September 2020 16:20 UTC](https://discourse.nodered.org/t/update-query-in-sqlite3-with-fob-id-coming-from-gateway-code/32292/135 "2020-09-09T16:20:36Z")

</div>

congratulations. I will close this topic

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [9 September 2020 16:20 UTC](https://discourse.nodered.org/t/update-query-in-sqlite3-with-fob-id-coming-from-gateway-code/32292/136 "2020-09-09T16:20:41Z")

</div>

Closed on 9 September 2020 16:20 UTC



[Previous page](https://discourse.nodered.org/t/update-query-in-sqlite3-with-fob-id-coming-from-gateway-code/32292.md?page=6)
