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