Using timestamp function in Postgres SQL while inserting json data

Question

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!

Adrian Klaver · Accepted Answer · 2022-10-23 16:00:31Z

1

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.

answered Oct 23, 2022 at 16:00

Adrian Klaver

20.4k3 gold badges24 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

jian · Accepted Answer · 2022-10-23 13:48:44Z

0

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
$$;

answered Oct 23, 2022 at 13:48

jian

5,0231 gold badge29 silver badges47 bronze badges

Collectives™ on Stack Overflow

Using timestamp function in Postgres SQL while inserting json data

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related