高斯DWS数据库日期时间格式转换-数据质量

背景

在项目中有时候需要对时间日期字段进行规范化，比如业务时间各种各样格式的都有，甚至有部分存在不是日期时间格式，需要规范化字段和剔除掉不可用的脏数据；

思路

利用case when先将能匹配到的格式数据进行格式化，无法进行格式化的数据统一赋值为数据格式不规范；

代码

case

-- yyyymm

when regexp_like(date_field,'^[0-9]{6}$') then to_char(to_date(date_field,'yyyyMM'),'yyyy-MM')

-- yyyy

when regexp_like(date_field,'^[0-9]{4}$') then to_char(to_date(date_field,'yyyy'),'yyyy')

-- yyyy-M

when REGEXP_LIKE(date_field, '^[0-9]{4}-[1-9]{1}$') then to_char(to_date(date_field,'yyyy-MM'),'yyyy-MM')

-- yyyy-MM

when REGEXP_LIKE(date_field, '^[0-9]{4}-[0-9]{2}$') then to_char(to_date(date_field,'yyyy-MM'),'yyyy-MM')

-- yyyy年MM月

when REGEXP_LIKE(date_field, '^[0-9]{4}年[0-9]{1,2}月$') then to_char(to_date(date_field,'yyyy年MM月'),'yyyy-MM')

-- yyyy-MM-dd

when REGEXP_LIKE(date_field, '^[0-9]{4}-[0-9]{2}-[0-9]{2}$') then date_field

-- yyyy

when REGEXP_LIKE(date_field, '^[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2}$') then date_field

-- yyyy-mm-dd hh24:mi:ss

when REGEXP_LIKE(date_field, '^[0-9]{4}/[0-9]{1,2}/[0-9]{1,2} [0-9]{1,2}:[0-9]{1,2}$') then TO_CHAR(TO_DATE(date_field, 'yyyy/mm/dd hh24:mi'), 'yyyy-mm-dd hh24:mi:ss')

-- yyyy-mm-dd hh24:mi

when REGEXP_LIKE(date_field, '^[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}$') then date_field

-- mm/dd/yy

when REGEXP_LIKE(date_field, '^\d{1,2}/\d{1,2}/\d{2}$') then TO_CHAR(TO_DATE(date_field, 'mm/dd/yy'), 'yyyy-mm-dd')

-- YYYY-MM-DD HH24:MI:SS.MS

when REGEXP_LIKE(date_field, '^[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2}\.[0-9]{1,6}') then TO_CHAR(TO_TIMESTAMP(substring(date_field,1,19), 'YYYY-MM-DD HH24:MI:SS.MS'), 'yyyy-MM-dd hh24:mi:ss')

-- YYYY-MM-DDTHH24:MI:SS.MS

when REGEXP_LIKE(date_field, '^[0-9]{4}-[0-9]{2}-[0-9]{2}T[0-9]{2}:[0-9]{2}:[0-9]{2}\.[0-9]{3}\+[0-9]{2}:[0-9]{2}')

then concat(substring(date_field,1,10),' ',substring(date_field,12,8))

-- yyyy/MM/dd

when REGEXP_LIKE(date_field, '^[0-9]{4}/[0-9]{2}/[0-9]{2}$') then TO_CHAR(TO_DATE(date_field, 'yyyy/MM/dd'), 'yyyy-MM-dd')

-- yyyyMMdd

when REGEXP_LIKE(date_field, '^[0-9]{4}[0-9]{2}[0-9]{2}$') then TO_CHAR(TO_DATE(date_field, 'yyyyMMdd'), 'yyyy-MM-dd')

else '数据格式不规范' end ywsj_update