PostgreSQL中JSONB内时间戳转日期格式并更新

问题场景 #

现有表t_activation_history，其中activation为JSONB类型字段，数据示例如下：

{
  "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"
}

需要将activation中的updateDate、statusUpdateDate字段从科学计数法格式的时间戳，转换为'YYYY-MM-DD HH24:MI:SS.MS'（如2023-06-23 14:35:42.249）格式的字符串并更新表数据。

解决方法 #

之前的尝试未将时间戳转换为指定格式的字符串，导致不符合需求或报错。正确的做法是先将科学计数法数值转成时间戳，再格式化为目标字符串，最后存入JSONB：

1. 更新单个字段（以updateDate为例） #

UPDATE t_activation_history
SET activation = activation || jsonb_build_object(
    'updateDate', 
    to_jsonb(to_char(to_timestamp((activation->>'updateDate')::numeric), 'YYYY-MM-DD HH24:MI:SS.MS'))
)
WHERE id = 1566;

2. 同时更新两个字段 #

UPDATE t_activation_history
SET activation = activation 
    || jsonb_build_object(
        'updateDate', 
        to_jsonb(to_char(to_timestamp((activation->>'updateDate')::numeric), 'YYYY-MM-DD HH24:MI:SS.MS'))
    )
    || jsonb_build_object(
        'statusUpdateDate', 
        to_jsonb(to_char(to_timestamp((activation->>'statusUpdateDate')::numeric), 'YYYY-MM-DD HH24:MI:SS.MS'))
    )
-- 可根据需要添加WHERE条件，比如指定id或批量更新
WHERE id = 1562;

关键步骤说明 #

• (activation->>'updateDate')::numeric：将JSONB中存储的科学计数法字符串转为数值类型，确保时间戳转换不报错
• to_timestamp(...)：将数值型时间戳转换为PostgreSQL的timestamp类型
• to_char(..., 'YYYY-MM-DD HH24:MI:SS.MS')：将timestamp格式化为指定的字符串格式，MS表示保留三位毫秒数，匹配需求中的格式
• to_jsonb(...)：将格式化后的字符串转为JSONB类型，确保能正确合并到原JSONB字段中

内容的提问来源于stack exchange，提问作者AJO