mysql数据库里的时间字段,如何不变动年月日,只改时分秒

 需求是把policy_info表内的 policy_end_time字段的时分秒都改成'23:59:59',年月日不变

下图是policy_info表内的 policy_end_time字段(timestamp类型),用Navicat打开的

 

 先是几个查询,可忽略

select count(*) from policy_info where RIGHT(policy_end_time,8) = '23:59:59';
select count(*) from policy_info where RIGHT(policy_end_time,8) = '00:00:00';
select count(*) from policy_info where policy_end_time is null;

 

select policy_end_time from policy_info where RIGHT(policy_end_time,8) = '00:00:00' limit 10;

SELECT
    DATE_ADD( policy_end_time, INTERVAL '23:59:59' HOUR_SECOND ) AS newDate 
FROM
    policy_info 
WHERE
    RIGHT ( policy_end_time, 8 ) = '00:00:00';

更新时分秒,此处用了 DATE_ADD函数,具体用法解析可参阅

MySQL DATE_ADD() 函数

mysql中date_add()函数的使用_dongxue2019的博客-CSDN博客

UPDATE policy_info
SET policy_end_time = DATE_ADD( policy_end_time, INTERVAL '23:59:59' HOUR_SECOND ) 
WHERE
    RIGHT ( policy_end_time, 8 ) = '00:00:00';

 

也可直接用如下方法更新

UPDATE policy_info
SET policy_end_time = DATE_ADD( LEFT ( policy_end_time, 10 ), INTERVAL '23:59:59' HOUR_SECOND ) 
WHERE
    policy_end_time IS NOT NULL 
    AND RIGHT ( policy_end_time, 8 ) != '23:59:59';

修改后的结果如下图