Mysql将excel表格时间格式yyyy-M-d H:mm:ss格式转为yyyy-MM-dd HH:mm:ss

未发哦京东发

已于 2025-01-21 14:20:13 修改

阅读量861

点赞数

分类专栏： mysql java开发工程师文章标签： mysql

于 2022-05-20 14:16:16 首次发布

本文链接：https://blog.csdn.net/weixin_37460672/article/details/124882400

版权

java开发工程师同时被 2 个专栏收录

28 篇文章

订阅专栏

mysql

3 篇文章

订阅专栏

1.项目中接收客户时间入库varchar类型
当时只发现 excel格式 2022/5/20 13:30:24 只简单将‘/’替换为‘-’，入库后
在这里插入图片描述
造成数据格式不正确查询不到数据按yyyy-MM-dd HH:mm:ss条件筛选
2.入库时时间格式判断并补‘0’

 if (bjsj.length() < 19) {
                String[] timeArray = bjsj.split(" ");
                String[] localDate = timeArray[0].split("/");
                String[] localTime = timeArray[1].split(":");
                for (int i = 1; i < localDate.length; i++) {
                    localDate[i] = StringUtils.leftPad(localDate[i], 2, "0");
                }
                for (int i = 0; i < localTime.length; i++) {
                    localTime[i] = StringUtils.leftPad(localTime[i], 2, "0");
                }
                String newDate = String.join("-", localDate);
                String newTime = String.join(":", localTime);
                bjsj = newDate + " " + newTime;
            } else {
                bjsj = bjsj.replaceAll("/", "-");
            }

原始代码只做了’/'替换‘-’

 bjsj = bjsj.replaceAll("/", "-");

3.将数据库历史数据时间修改按‘-’和‘:’截取不足两位补‘0’
3.1 分析截取判断长度小于2补‘0’

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(bjsj,' ',1),'-',1),
CASE WHEN
LENGTH(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(bjsj,' ',1),'-',2),'-',-1))<2 then CONCAT('0',SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(bjsj,' ',1),'-',2),'-',-1))
ELSE SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(bjsj,' ',1),'-',2),'-',-1) end,

CASE WHEN
LENGTH(SUBSTRING_INDEX(SUBSTRING_INDEX(bjsj,' ',1),'-',-1))<2 then CONCAT('0',SUBSTRING_INDEX(SUBSTRING_INDEX(bjsj,' ',1),'-',-1))
ELSE SUBSTRING_INDEX(SUBSTRING_INDEX(bjsj,' ',1),'-',-1) end,

CASE WHEN
LENGTH(SUBSTRING_INDEX(SUBSTRING_INDEX(bjsj,' ',-1),':',1))<2 then CONCAT('0',SUBSTRING_INDEX(SUBSTRING_INDEX(bjsj,' ',-1),':',1))
ELSE SUBSTRING_INDEX(SUBSTRING_INDEX(bjsj,' ',-1),':',1) end,

CASE WHEN
LENGTH(SUBSTRING_INDEX(SUBSTRING_INDEX(bjsj,':',2),':',-1))<2 then CONCAT('0',SUBSTRING_INDEX(SUBSTRING_INDEX(bjsj,':',2),':',-1))
ELSE SUBSTRING_INDEX(SUBSTRING_INDEX(bjsj,':',2),':',-1) end,

CASE WHEN
LENGTH(SUBSTRING_INDEX(bjsj,':',-1))<2 then CONCAT('0',SUBSTRING_INDEX(bjsj,':',-1))
ELSE SUBSTRING_INDEX(bjsj,':',-1) end
from car_in_out_alert where id =2222

在这里插入图片描述
3.2 拼接时间字符串

UPDATE car_in_out_alert set bjsj = 
CONCAT(
SUBSTRING_INDEX(SUBSTRING_INDEX(bjsj,' ',1),'-',1),
'-',
CASE WHEN
LENGTH(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(bjsj,' ',1),'-',2),'-',-1))<2 then CONCAT('0',SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(bjsj,' ',1),'-',2),'-',-1))
ELSE SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(bjsj,' ',1),'-',2),'-',-1) end,
'-',
CASE WHEN
LENGTH(SUBSTRING_INDEX(SUBSTRING_INDEX(bjsj,' ',1),'-',-1))<2 then CONCAT('0',SUBSTRING_INDEX(SUBSTRING_INDEX(bjsj,' ',1),'-',-1))
ELSE SUBSTRING_INDEX(SUBSTRING_INDEX(bjsj,' ',1),'-',-1) end,
' ',
CASE WHEN
LENGTH(SUBSTRING_INDEX(SUBSTRING_INDEX(bjsj,' ',-1),':',1))<2 then CONCAT('0',SUBSTRING_INDEX(SUBSTRING_INDEX(bjsj,' ',-1),':',1))
ELSE SUBSTRING_INDEX(SUBSTRING_INDEX(bjsj,' ',-1),':',1) end,
':',
CASE WHEN
LENGTH(SUBSTRING_INDEX(SUBSTRING_INDEX(bjsj,':',2),':',-1))<2 then CONCAT('0',SUBSTRING_INDEX(SUBSTRING_INDEX(bjsj,':',2),':',-1))
ELSE SUBSTRING_INDEX(SUBSTRING_INDEX(bjsj,':',2),':',-1) end,
':',
CASE WHEN
LENGTH(SUBSTRING_INDEX(bjsj,':',-1))<2 then CONCAT('0',SUBSTRING_INDEX(bjsj,':',-1))
ELSE SUBSTRING_INDEX(bjsj,':',-1) end
)
where LENGTH(bjsj)<19