Node red write into postgres in other format

General
1

i got a node that insert a line into the database

INSERT INTO public."TB_ALERTE"("TR_SENSORNAME", "TR_INFO","TR_DATEHEURE") VALUES ( 'SENSOR/ TOILETTE' , 'ON','{{msg.payload}}');
the payload is coming from a function

const d = new Date();
msg.payload = d.toLocaleString();
return msg;

but the problem is the value written in the db is not readable

6& #x2F;27& #x2F;2025, 10:36:23 AM

i put some space in the text because your website correct it

how can i be able to see the date like that
6/27/2025, 11:10:22 AM

thanks

2

Welcome to the forum @bobdion.

What datatype is your TR_DATEHEURE field?

For timestamp fields, since you are trying to store the current date/time, you could use the Progres current_timestamp function
So that would be something like

INSERT INTO public."TB_ALERTE"("TR_SENSORNAME", "TR_INFO","TR_DATEHEURE") 
VALUES ( 'SENSOR/ TOILETTE' , 'ON', CURRENT_TIMESTAMP);
3

the field is a text not a timestamp

4

If you send the function node output to a debug node, what does it show?

I get this but of course my locale may be different to yours.

image
image620Ă—264 16.6 KB

5

yes the result is good

6

image
image833Ă—231 8.34 KB

image

7

Looks like the postgres Database or something else is converting special characters to Unicode Hex Character Code / (/).

Saying that you should store all timestamps in a database as universal time, and when you need to display read the universal time and convert to local time. This will cause you less issues further down the line.

8

ok but the problem will i get other problem like that with different function
there is a way to change that in postgres

9

Yes the unicode representation of / is /.

Postgres cannot store unicode, it needs utf-8 encoded strings.
It's not clear where the string gets unicode encoded.

Can you execute SHOW SERVER_ENCODING in Progres?
Which node-red config node are you using? Does the config allow an encoding setting?

10

i did what you say with current_timestamp

and its working

i will leave it like that and if in the future i got another problem i will come back here

lol

thanks

1 Like