Convert date from timestamp to date in jsonb in postgres

Question

I have a table like this :

"id": 1562,
"creation_date": "2023-06-23 14:35:42.249",
activation": {
    "updateDate": 1.687523742249E9,
    "euid": "test",
    "statusUpdateDate": 1.687523742249E9,
    "standalone": false,
    "variationCode": null,
    "idohp": "test",
    "creationDate": 1.687523742244E9,
    "partnerVariationValue": null,
    "variableCharacteristics": null,
    "basicProduct": "test",
    "variationValue": null,
    "partner": "test",
    "updateSource": "test",
    "partnerTransactionId": null,
    "noEuidReuse": false,
    "id": 496,
    "status": "CREATED"
},
"history_version": 1,
"update_date": "2023-06-23 14:35:42.249"

I want to update updateDate, statusUpdateDate from the timestamp format to 2023-06-23 14:35:42.249 format

I have tried some combination of to_date and to_timestamp but i am always getting an error due to the timestamp format and could not find a way to solve this

EDIT

to update one json field here is the request

update t_activation_history set activation = activation || (jsonb_build_object('updateDate', to_jsonb(to_timestamp((activation->>'updateDate')::numeric)))) where id = 1566;

Answer 1

Both object.activation.statusUpdateDate and object.activation.updateDate are epoch timestamps - seconds after 1970-01-01 00:00Z. to_timestamp converts these to timestamptz. So extract the values as numeric and use to_timestamp function. Here is an illustration with your sample data as t CTE. Please note the use of jsonb_set two times.

with t(j) as
(
 values
 ('{
  "id": 1562,
  "creation_date": "2023-06-23 14:35:42.249",
  "activation": {
    "updateDate": 1.687523742249E9, "euid": "test", 
    "statusUpdateDate": 1.687523742249E9, "standalone": false,
    "variationCode": null, "idohp": "test", 
    "creationDate": 1.687523742244E9, "partnerVariationValue": null,
    "variableCharacteristics": null, "basicProduct": "test",
    "variationValue": null, "partner": "test", 
    "updateSource": "test", "partnerTransactionId": null,
    "noEuidReuse": false, "id": 496, "status": "CREATED"
  },
  "history_version": 1,
  "update_date": "2023-06-23 14:35:42.249"}'::jsonb
 )
)
select jsonb_set(
         jsonb_set(
                   j, 
                   '{activation, statusUpdateDate}', 
                   to_jsonb(to_timestamp((j->'activation'->>'statusUpdateDate')::numeric))
                  ),
         '{activation, updateDate}',
         to_jsonb(to_timestamp((j->'activation'->>'updateDate')::numeric))
)
from t;

{
  "id": 1562,
  "activation": {
    "id": 496,
    "euid": "test",
    "idohp": "test",
    "status": "CREATED",
    "partner": "test",
    "standalone": false,
    "updateDate": "2023-06-23T15:35:42.249+03:00",
    "noEuidReuse": false,
    "basicProduct": "test",
    "creationDate": 1687523742.244,
    "updateSource": "test",
    "variationCode": null,
    "variationValue": null,
    "statusUpdateDate": "2023-06-23T15:35:42.249+03:00",
    "partnerTransactionId": null,
    "partnerVariationValue": null,
    "variableCharacteristics": null
  },
  "update_date": "2023-06-23 14:35:42.249",
  "creation_date": "2023-06-23 14:35:42.249",
  "history_version": 1
}

To make a long story short - extract the epoch timestamp value as text, cast it as numeric and call to_timestamp with the resulting number.

Edit
A bit shorter using || operator

select j || (j -> 'activation' 
         ||  jsonb_build_object('statusUpdateDate', to_jsonb(to_timestamp((j->'activation'->>'statusUpdateDate')::numeric)))
         ||  jsonb_build_object('updateDate', to_jsonb(to_timestamp((j->'activation'->>'updateDate')::numeric))))
from t;