Script to update corrupted metadata updatedDate for finance records

When to apppy?

This fix should by applied when “metadata.updatedDate” has numeric format instead of date format like in this example in mod-finance-storage transactions table:

"metadata": {
    "createdDate": "2020-07-20T13:46:18.225",
    "updatedDate": 1595883484581,
    "createdByUserId": "fa67c831-9276-4d01-9b71-66e70aaada9e",
    "updatedByUserId": "d1799949-a868-4ffa-9b11-9fd686459b46"

Steps to fix

First Verify corrupted ones with this query. All of them should be 67(didn't remember exact number) as we checked previously, change to correct tenantid

SELECT 
    id,
    jsonb->'metadata'->'updatedDate' AS epoch_updated_date,
    to_char(
        to_timestamp((jsonb->'metadata'->>'updatedDate')::bigint / 1000), 
        'YYYY-MM-DD"T"HH24:MI:SS.MS"Z"'
    ) AS iso_updated_date
FROM diku_mod_finance_storage.transaction
WHERE 
    jsonb->'metadata'->'updatedDate' IS NOT NULL
    AND jsonb->'metadata'->>'updatedDate' ~ '^[0-9]+$'
    AND length(jsonb->'metadata'->>'updatedDate') > 10;

Next run query , it should update all records as we found previously

UPDATE diku_mod_finance_storage.transaction
SET jsonb = jsonb_set(
    jsonb,
    '{metadata,updatedDate}',
    to_jsonb(
        to_char(
            to_timestamp((jsonb->'metadata'->>'updatedDate')::bigint / 1000), 
            'YYYY-MM-DD"T"HH24:MI:SS.MS"Z"'
        )
    )
)
WHERE 
    jsonb->'metadata'->'updatedDate' IS NOT NULL
    AND jsonb->'metadata'->>'updatedDate' ~ '^[0-9]+$'
    AND length(jsonb->'metadata'->>'updatedDate') > 10;

Finally check again if there are no corrupted records after update

SELECT 
    id,
    jsonb->'metadata'->'updatedDate' AS epoch_updated_date,
    to_char(
        to_timestamp((jsonb->'metadata'->>'updatedDate')::bigint / 1000), 
        'YYYY-MM-DD"T"HH24:MI:SS.MS"Z"'
    ) AS iso_updated_date
FROM diku_mod_finance_storage.transaction
WHERE 
    jsonb->'metadata'->'updatedDate' IS NOT NULL
    AND jsonb->'metadata'->>'updatedDate' ~ '^[0-9]+$'
    AND length(jsonb->'metadata'->>'updatedDate') > 10;