Guess this is java related: datetime

[AndKe]

I am trying to populate a mysql field that expects datetime. my server is configured for normal 24h system:

$ date
lø. 24. des. 10:50:41 +0100 2022

I tried to follow this example:
https://flows.nodered.org/flow/59fe2502dd82ae9b8a55b949a48e3d89

Using the Date().toISOString()
results in:"Error: Incorrect datetime value: '2022-12-24T07:10:45.752Z' for column 'timestamp' at row 1"

I tried Date().toLocaleString()
and I get this error: "Error: Incorrect datetime value: '12/24/2022, 9:52:32 AM' for column 'timestamp' at row 1"
... no wonder with that AM/PM mess.
Please suggest a solution

[E1cid]

According to the MYSQL docs the datetime field format is YYYY-MM-DD HH:mm:ss with optional fractional seconds to 6 digits .SSSSSS.

So try removing the T
https://dev.mysql.com/doc/refman/8.0/en/datetime.html#:~:text=The%20DATETIME%20type%20is%20used,both%20date%20and%20time%20parts.

[Steve-Mcl]

I would advise you avoid building SQL queries to avoid SQLi hacks & instead use a parameterised query as documented in the readme

I suspect you could simply pass the date object without calling toString

[AndKe]

@Steve-Mcl - thank you - you have a good point there.

The example says:

msg.payload=[24, 'example-user'];
msg.topic="INSERT INTO users (`userid`, `username`) VALUES (?, ?);"
return msg;

if my mqtt payload is { "battery": 100, "humidity": 87.36, "linkquality": 150, "linkquality": 147, "power_outage_count": 11, "pressure": 1002, "temperature": -1.06, "pressure": 1002.2, "temperature": -1.07, "voltage": 3085 }

• how can I refer to some selected values?
  I mean to do something like: msg.topic="INSERT INTO mqtt (`timestamp`, `temperature`, `humidity`) VALUES (Date().toISOString().slice(0, 19).replace('T', ' ') , ??????)
  ...
  I do not understand the ?,? in the example - how do I complete my "msg.topic" with temperature and humidity from the incoming mqtt array ?

(the slice'n'replace apparently works for MySQL)

[Steve-Mcl]

Do exactly as the example did but use your SQL and have three ? question marks and set the payload to the 3 values you want

msg.topic = "INSERT INTO mqtt (`timestamp`, `temperature`, `humidity`) VALUES (?,?,?)" 
msg.payload=[
  new Date(),
  msg.payload.temperature,
  msg.payload.humidity
]
return msg

[AndKe]

@Steve-Mcl thank you - it works as expected - but I am puzzled over the fact that each update creates three db updates, with old, and new data.

[Colin]

Add a debug node showing all the messages going into the db node and make sure there is only one message for each one.

[AndKe]

Please note three messages at the same time - for this one sensor.

worse: it seems those come come from zigbee2mqtt:

[Colin]

In that case, probably, the sensor is publishing it three times.
You could use a delay node set to allow 1 message every 10 seconds and to discard intermediate messages. That would throw away the repeats.

[AndKe]

solved by using the "debouce" feature of zigbee2mqtt
thank you.

[Colin]

I didn't know about that feature, it could be useful, thanks.