Using timestamp function in Postgres SQL while inserting json data-CodePudding

I am trying to insert timestamp into json data into a column called 'conversations' of type "jsonb"

this is what I want the data to look like-

{
"sender": "John",
"message": "Issue",
"msgAt": "2022-11-11"}

Below is my attempt at the above,which is not the correct format for the function NOW()

    INSERT INTO ticket_data(conversations) VALUES('{"sender":"John","message":"Issue","msgAt":NOW()}');

Any help is appreciated!

CodePudding user response：

From JSON functions Table 9.47. JSON Creation Functions.

select json_build_object('sender', 'John', 'message', 'Issue', 'msgAt', current_date);
                        json_build_object                         
------------------------------------------------------------------
 {"sender" : "John", "message" : "Issue", "msgAt" : "2022-10-23"}

Changed now() to current_date as that was what you wanted to end up with.

CodePudding user response：

select ('{"sender":"John","message":"Issue",' || '"msgAt":'|| '"' || ( now()::date) ||'"' ||'}')::json;

or use parameter.

DO $$
DECLARE
    _date date;
BEGIN
    _date := now();
    RAISE NOTICE '%', json_build_object('sender', 'John', 'message', 'Issue', 'msgAt', _date);
END
$$;