Update query in sqlite3 with fob id coming from gateway code

General
121

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.

122

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.

123

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

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

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

125

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

126

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.

127

Hello Sir Good Morning,I have tried it

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:

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

please suggest.

128

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?

129

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

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

command

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

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

130

Sir i have done successfully with time also

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

131

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.

1 Like
132

@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.

133

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

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

Sir thank you for guidance.now out out is coming

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

congratulations. I will close this topic