明确一点:
pg中字符串转date 用的是to_timestamp(‘2021-08-24 18:33:41’,‘yyyy-MM-dd hh24:mi:ss’)
而非 to_date(‘2021-08-24 18:33:41’,‘yyyy-MM-dd hh24:mi:ss’);
示例:
修改 library 的日期和操作时间中的月份由7月改为8月,其余不变
update library set "day"='20210828', operation_time = to_timestamp(CONCAT (to_char(operation_time,'yyyy'),'-08-',to_char(operation_time,'dd '),to_char(operation_time,'HH24:MI:SS')),'yyyy-MM-dd hh24:mi:ss') where "day"='20210728';
不加限制条件且只修改月份
update library set operation_time = to_timestamp(CONCAT (to_char(operation_time,'yyyy'),'-08-',to_char(operation_time,'dd '),to_char(operation_time,'HH24:MI:SS')),'yyyy-MM-dd hh24:mi:ss') ;