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

4935同学

于 2024-04-12 17:42:39 发布

阅读量1k

点赞数 4

分类专栏： SQL 文章标签：数据库 linux

本文链接：https://blog.csdn.net/weixin_43929753/article/details/137689192

版权

SQL 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

本文介绍了在项目中如何通过SQL的CASEWHEN语句和正则表达式对不同格式的时间日期字段进行规范化处理，以及如何标记格式不规范的数据。

摘要由CSDN通过智能技术生成

背景

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

思路

利用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