How to change existing timestamp in JSONB object to another timestamp format in Postgresql

user_1822712 0
Viewed 246

Table user_record has column info which is returning following data whereas info is a jsonb column.
QUERY: Select info from user_record;
Result:

{"someId": "XXFUN0123XX", "age": 43, "updatedAt": "2017-02-18 00:00:00"}

Now The question is specifically about updatedAt field from json response
select info ->> 'updatedAt' from user_record where id='XXFUN0123XX';
Result: 2017-02-18 00:00:00

I have to update all the timestamps of updatedAt fields of info column of user_record table with this format -> YYYY-MM-DDTHH:MI:SS+02:00 so when I do select info ->> 'updatedAt', I would get the result like this -> 2017-02-18T00:00:00+02:00.
I've already tried multiple ways and read the documentation but couldn't find a proper solution. Following is one query which I was trying to do 

UPDATE user_record SET info = info || jsonb_build_object('updatedAt', 
select to_timestamp(info ->> 'updatedAt','YYYY-MM-DDTHH:MM:SS+02:00') from user_record where id ='XXFUN0123XX')) where id = 'XXFUN0123XX';

In above query, I am trying to pick up updatedAt from json object from inner query.

postgresql-9.5 postgresql
1 Answers

use jsonb_set to make the changes in the jsonb. and use to_char instead of to_timestamp like below:

update user_record
set info=jsonb_set(info,'{updatedAt}',to_jsonb(to_char((info ->> 'updatedAt')::timestamp,'YYYY-MM-DD"T"HH24:MI:SS+02:00')))

DEMO

user_77724830
Related