Postgres write timestamp error

[kgakere]

Hi, i have a postgesdb that i am trying to inject a timestamp into a column.
I have used a function node to convert the time to an appropriate format but still have errors.
Kindly assist;

error: syntax error at or near "20"
2022-12-23 20:10:57.716

[Buckskin]

Try removing the SSS from the format. I do not use postgres but looking at the allowed date formats none of them include anything other than YYY-MM-DD HH:mm:ss.

You could also try epoch if that is available to you as that seem as though it may be valid

[E1cid]

You can not $now().format() You can $moment().format()
But maybe we are not seeing all the picture.

[kgakere]

Tried removing the millisec but still the error is persisting

[kgakere]

[
    {
        "id": "5a2b67f8e1276282",
        "type": "tab",
        "label": "Flow 8",
        "disabled": false,
        "info": "",
        "env": []
    },
    {
        "id": "20f3f0eca8369002",
        "type": "postgresql",
        "z": "5a2b67f8e1276282",
        "name": "",
        "query": "INSERT INTO water_level_tank1(time) VALUES ({{msg.payload}}); \n",
        "postgreSQLConfig": "b91b213bbbb40562",
        "split": false,
        "rowsPerMsg": 1,
        "outputs": 1,
        "x": 730,
        "y": 220,
        "wires": [
            [
                "3f6eb8fe286e1404"
            ]
        ]
    },
    {
        "id": "3f6eb8fe286e1404",
        "type": "debug",
        "z": "5a2b67f8e1276282",
        "name": "debug 11",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "false",
        "statusVal": "",
        "statusType": "auto",
        "x": 900,
        "y": 220,
        "wires": []
    },
    {
        "id": "c6af5f21fd73e3fe",
        "type": "inject",
        "z": "5a2b67f8e1276282",
        "name": "",
        "props": [
            {
                "p": "payload"
            }
        ],
        "repeat": "",
        "crontab": "",
        "once": false,
        "onceDelay": 0.1,
        "topic": "",
        "payloadType": "str",
        "x": 290,
        "y": 180,
        "wires": [
            [
                "6c3a8e033f6fb0f4"
            ]
        ]
    },
    {
        "id": "a5bef75bc183fb2b",
        "type": "debug",
        "z": "5a2b67f8e1276282",
        "name": "",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "false",
        "statusVal": "",
        "statusType": "auto",
        "x": 910,
        "y": 140,
        "wires": []
    },
    {
        "id": "6c3a8e033f6fb0f4",
        "type": "change",
        "z": "5a2b67f8e1276282",
        "name": "",
        "rules": [
            {
                "t": "set",
                "p": "payload",
                "pt": "msg",
                "to": "$moment().format('YYYY-MM-DD HH:mm:ss')",
                "tot": "jsonata"
            }
        ],
        "action": "",
        "property": "",
        "from": "",
        "to": "",
        "reg": false,
        "x": 500,
        "y": 180,
        "wires": [
            [
                "a5bef75bc183fb2b",
                "20f3f0eca8369002"
            ]
        ]
    },
    {
        "id": "b91b213bbbb40562",
        "type": "postgreSQLConfig",
        "name": "",
        "host": "127.0.0.1",
        "hostFieldType": "str",
        "port": "5432",
        "portFieldType": "num",
        "database": "postgres",
        "databaseFieldType": "str",
        "ssl": "false",
        "sslFieldType": "bool",
        "applicationName": "",
        "applicationNameType": "str",
        "max": "10",
        "maxFieldType": "num",
        "idle": "1000",
        "idleFieldType": "num",
        "connectionTimeout": "10000",
        "connectionTimeoutFieldType": "num",
        "user": "postgres",
        "userFieldType": "str",
        "password": "abcd1234",
        "passwordFieldType": "str"
    }
]

[bakman2]

@kgakere

INSERT INTO water_level_tank1(time) VALUES ({{msg.payload}})

Could you try instead (timestamp should be inserted as a string):

INSERT INTO water_level_tank1(time) VALUES ('{{{ msg.payload }}}')

note i don't use postgres, but this is what documentation states.

[Colin]

Are you trying to add the current time? If so then I would be surprised if Postgres does not have an option to automatically set a column to the current time when the record is added.

[kgakere]

Yes there is a default way to insert timestamp.
Thanks for pointing me in the right direction.