Best way to add a timestamp to the "msg" object?

[geoff]

What is the best (simplest) way a timestamp to the "msg" object?

I have data in "msg.payload" and I wish to add a timestamp. The goal is to add the data and the timestamp to an sqlite database.

Would you please say if the following method is or is not a good solution and why?

Using the change node I first move the contents of "msg.payload" to "msg.sensordata" then I set "msg.payload" to timestamp.

This looks like it will work, but is there a better way?

[Hubertus]

I use a Node simpletime for this
https://flows.nodered.org/node/node-red-contrib-simpletime

[Colin]

Don't you need the timestamp inside the structure inside the payload, along with the other fields ready to write to the database?

If so, then in the Change node Set msg.payload.timestamp To the timestamp milliseconds.

You can do without the JSON node by select Auto mode in the MQTT node so that it automatically convert it to an object.

[jbudd]

Does sqlite not have the ability to set the timestamp as a default value?

[geoff]

Thank you for your reply.

Setting msg.payload.timestamp did not work for me, I'm interested in what I'm doing wrong?

"Cannot set property of non-object type: payload.timestamp"

[geoff]

Thank you, it does.

How to have an automatic timestamp in SQLite?

[smanjunath211]

I believe you need to first create payload, and then only can add anything else

[Colin]

Take out the JSON node. I suspect that the MQTT node is already outputting an object, and the JSON node is converting it to a string.

However, as @jbudd suggests, I think you can have a timestamp field in the database and tell it to automatically insert the current time when you add the record, so have a look at that too.

[smanjunath211]

I too use localtime() to add a timestamp into mysql table while logging, however, if the timestamp you require is not the time of 'writing' into database, but the time the data got acquired from a flow, (if you are purposefully delaying the db write), it may make a difference. doesn't it ?

[geoff]

Great, that is it.
"Take out the JSON node. I suspect that the MQTT node is already outputting an object, and the JSON node is converting it to a string."

Thank you for spotting that.

[geoff]

I would like to say, that was the plan, but I got so focused on node red I did not consider doing it in the db.

While jbudd suggestion is a very good approach, I still like the idea of setting the timestamp in node red.

[geoff]

Thank you for pointing this out.
I think It is a good reminder not to be so focused on one approach.

[jbudd]

Just for the sake of completeness, you can have the database store the timestamp at UPDATE as well as INSERT.

Here is a snippet from my SQLite table definitions.
A trigger updates the lastseen field of Table1 when a Table2 record is updated:

CREATE TABLE Table2 (mmsi varchar(10) NOT NULL PRIMARY KEY, 
   timestamp DATETIME DEFAULT CURRENT_TIMESTAMP, 
   blah, blah);
CREATE TRIGGER updatelastseen AFTER UPDATE ON Table2
BEGIN
UPDATE Table1 SET lastseen = CURRENT_TIMESTAMP 
WHERE mmsi = old.mmsi;
END;