PostgreSQL JSON字段混合YYYY-MM-DD和13位Unix时间戳统一转换问题

问题根因 #

你遇到的报错是因为else分支的日期解析模板与实际输入格式不匹配：你当前存储的字符串日期为YYYY-MM-DD格式（年-月-日带横杠分隔符），但你写的解析模板是MMDDYYYY（要求输入为月日年拼接的无分隔符字符串），格式不匹配导致解析失败。

修复后可正常执行的语句（输出日期类型） #

case 
    when x.closedate::text ~* '^[0-9]{13}$' then to_timestamp(x.closedate::bigint/1000)
    when x.closedate in ('0', '', null) then null
    else to_date(x.closedate, 'YYYY-MM-DD') 
end as transactionclosedate

按需求统一转为数值类型的最终版本 #

你要求最终输出为数值类型而非字符串，可以将两种格式的日期统一转为13位毫秒级Unix时间戳数值：

case 
    when x.closedate::text ~* '^[0-9]{13}$' then x.closedate::bigint
    when x.closedate in ('0', '', null) then null
    else extract(epoch from to_date(x.closedate, 'YYYY-MM-DD'))::bigint * 1000
end as transactionclosedate

• extract(epoch from 日期) 会返回对应日期的10位秒级Unix时间戳，乘以1000后即可和13位毫秒级时间戳的数值格式对齐
• 最终输出为bigint类型的数值，无需字符串存储

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