Payload.date string change himself to another format when build string variable

at the output of a function my payload is:
{"variable":"index","value":1,"date":"2023-11-20T11:56:53.282Z","id_mesure":653}

I pass this to an other function
the payload stay the same but when extract the date string format change:

requete_sql = "INSERT INTO t_data (mesure_FK,date_heure,b) VALUES (" + id_mesure + ",'" + msg.payload.date + "'," + valeur + ") ON DUPLICATE KEY UPDATE b=" + valeur;

now the string requete_sql is:
"INSERT INTO t_data (mesure_FK,date_heure,b) VALUES (653,'Mon Nov 20 2023 12:56:53 GMT+0100 (heure normale d’Europe centrale)',1) ON DUPLICATE KEY UPDATE b=1"

so the date format changed to Mon Nov 20 2023 12:56:53 GMT+0100 (heure normale d’Europe centrale)

(I tried to use also an variable to store temporaly the msg.payload.date bur is the same)
an idea please ?

Welcome to the forums @Phildu77

Its because the debug pane is rendering the Date object to an ISO string via Date.toJSON()
where as when you print as a string or it's part of another string it will call Date.toString()

And both of these produce different formats.
You need to format the date to the required output before including it as part of another string (such as an SQL statement)

If the ISO string is the format you need.

msg.payload.date.toJSON()
requete_sql = "INSERT INTO t_data (mesure_FK,date_heure,b) VALUES (" + id_mesure + ",'" + msg.payload.date.toJSON() + "'," + valeur + ") ON DUPLICATE KEY UPDATE b=" + valeur;

Personally I would prefer to use
msg.payload.date.toISOString()

They have the same result but toISOString() is a bit clearer in what it will do. It is purely a matter of personal taste though.

1 Like

Is the better option actually, given it's not really going out as a JSON object.

Perfect :slight_smile:
thanks a lot for your reactivity.
both solutions work

1 Like

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.