PostgreSQL如何将JSONB对象中的原有时间戳转换为指定时间格式

原语句错误说明 #

• 不需要嵌套子查询：UPDATE语句操作当前行时，可直接引用当前行的info字段值，无需额外查询同表数据
• 时间转换逻辑错误：to_timestamp的第二个参数是输入时间字符串的匹配格式，不是目标输出格式，需先正确解析原时间格式，再用to_char转换为目标格式

正确实现代码 #

单条指定ID更新 #

UPDATE user_record 
SET info = info || jsonb_build_object(
    'updatedAt', 
    to_char(
        to_timestamp(info ->> 'updatedAt', 'YYYY-MM-DD HH24:MI:SS'), 
        'YYYY-MM-DD"T"HH24:MI:SS+02:00'
    )
) 
WHERE id = 'XXFUN0123XX' AND info ? 'updatedAt';

全表所有行更新 #

UPDATE user_record 
SET info = info || jsonb_build_object(
    'updatedAt', 
    to_char(
        to_timestamp(info ->> 'updatedAt', 'YYYY-MM-DD HH24:MI:SS'), 
        'YYYY-MM-DD"T"HH24:MI:SS+02:00'
    )
)
WHERE info ? 'updatedAt';

代码说明 #

• to_timestamp(info ->> 'updatedAt', 'YYYY-MM-DD HH24:MI:SS')：将JSON中取出的字符串类型时间，按照现有格式2017-02-18 00:00:00解析为timestamp类型
• to_char(..., 'YYYY-MM-DD"T"HH24:MI:SS+02:00')：将timestamp转换为目标格式，格式中的T用双引号包裹避免被识别为格式占位符，末尾直接拼接固定的+02:00时区后缀
• info ? 'updatedAt'：过滤掉不存在updatedAt字段的行，避免空值解析报错

验证方法 #

执行更新后运行查询语句验证结果：

SELECT info ->> 'updatedAt' FROM user_record WHERE id='XXFUN0123XX';

返回结果为2017-02-18T00:00:00+02:00即符合要求。

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