Hive 日期合法性校验

linzeyu

已于 2023-05-07 13:25:24 修改

阅读量825

点赞数

文章标签： hive hadoop 日期 sql

于 2023-05-07 12:48:28 首次发布

本文链接：https://blog.csdn.net/linzeyu/article/details/130541332

版权

使用 date_format() 函数转换非法日期时会出现日期偏移，通过比较转换前后的日期是否一致时间日期合法性的校验

判断身份证中的日期是否合法

WITH t1 AS (  
    SELECT substr('XXXXXX20000228XXXX', 7, 8) birth union all  
    SELECT substr('XXXXXX20000229XXXX', 7, 8) birth union all  
    SELECT substr('XXXXXX20000230XXXX', 7, 8) birth union all  
    SELECT substr('XXXXXX19000228XXXX', 7, 8) birth union all  
    SELECT substr('XXXXXX19000229XXXX', 7, 8) birth union all  
    SELECT substr('XXXXXX20000531XXXX', 7, 8) birth union all  
    SELECT substr('XXXXXX20000532XXXX', 7, 8) birth  
)
SELECT birth AS before
     , date_format(from_unixtime(unix_timestamp(birth, 'yyyymmdd'), 'yyyy-mm-dd'), 'yyyyMMdd')  AS after
     , birth = date_format(from_unixtime(unix_timestamp(birth, 'yyyymmdd'), 'yyyy-mm-dd'), 'yyyyMMdd') AS compare
from t1;

执行结果

NO	before	after	compare
1	2000-02-28	2000-02-28	true
2	2000-02-29	2000-02-29	true
3	2000-02-30	2000-03-01	false
4	1900-02-28	1900-02-28	true
5	1900-02-29	1900-03-01	false
6	2000-05-31	2000-05-31	true
7	2000-05-32	2000-06-01	false