Phoenix时间日期格式转换为"yyyy-MM-dd HH:mm:ss"
问题描述
Phoenix数据库表create_time字段时间格式在导入数据过程中发生了格式错误:2022-03-1709:42:29,我们想把格式转换成正确的格式:2022-03-17 09:42:29,Phoenix不像MySQL那样拥有功能强大的时间格式化函数,我们实现时间格式化需要采用Phoenix的常用函数。
我们需要了解的几个Phoenix函数
- SUBSTR ,SUBSTR(“create_time”,0, 10)截取前十位字符串;
- LPAD,LPAD(SUBSTR(“create_time”,11, 9),9,’ ')截取时间最后几位且最左侧加空格;
- LENGTH,字符串长度;
实现过程
- 先根据id查询出某一条存在问题的数据且拼接成我们需要的正确格式
SELECT "id",SUBSTR("create_time",0, 10) ||LPAD(SUBSTR("create_time",11, 9),9,' ') AS "create_time" ,"create_time" AS care,LENGTH ("create_time")
FROM OAM_DCIM.ACTIVE_POWER_HIS WHERE "id" ='119223370392630990807';
- 查询create_time格式不正确的所以数据
SELECT "id",SUBSTR("create_time",0, 10) ||LPAD(SUBSTR("create_time",11, 9),9,' ') AS "create_time" ,"create_time" AS care,LENGTH ("create_time")
FROM OAM_DCIM.ACTIVE_POWER_HIS WHERE LENGTH ("create_time")= 18
- 更新所有格式不正确的时间
UPSERT INTO OAM_DCIM.ACTIVE_POWER_HIS ("id","create_time")
SELECT "id",SUBSTR("create_time",0, 10) ||LPAD(SUBSTR("create_time",11, 9),9,' ') AS "create_time"
FROM OAM_DCIM.ACTIVE_POWER_HIS WHERE LENGTH ("create_time")= 18