记录一个 Hive 查询中,关于 不规则时间/日期格式比较 的问题。
在数仓实施时,数据源中关于 时间/日期类型 的字段格式有很多,例如
2022-05-10
、
2022-05-10 15:30:15
、
2022/05/10
、
2022/05/10 15:30:15
,还有很多不规范的数据,例如
2022-5-10
、
2022-5-8 15:30:15
等等 。
我们在进行日期大小比较时,如果直接使用这些原始数据,就很容易出现问题。
看下面的例子:
# 直接用 时间字符串 比对查询,返回结果为 false
select '2022-5-10 15:30:15' > '2022-5-9 15:30:15';
# 直接用 时间字符串 比对查询,返回结果为 true
select '2022-5-10 15:30:15' > '2022-5-09 15:30:15';
# 假如一张表 test_04 ,有两个字段 date_1 、 date_2,都存储着时间类型数据。
test_04.date_1 test_04.date_2
1 2022-05-10 2022-05-09
2 2022-05-10 15:30:15 2022-05-09 15:30:15
3 2022-5-10 2022-5-9
4 2022-5-10 15:30:15 2022-5-9 15:30:15
# 情况一:这里时间比对时,当时间不规范比对结果有问题
SELECT * from test.test_04 where date_1 > date_2 limit 10;
1 2022-05-10 2022-05-09
2 2022-05-10 15:30:15 2022-05-09 15:30:15
# 情况二:如果转换为标准时间格式,比对结果没有问题
SELECT * from test.test_04 where date_format(date_1,'yyyy-mm-dd hh:MM:ss') > date_format(date_2,'yyyy-mm-dd hh:MM:ss') limit 10 ;
1 2022-05-10 2022-05-09
2 2022-05-10 15:30:15 2022-05-09 15:30:15
3 2022-5-10 2022-5-9
4 2022-5-10 15:30:15 2022-5-9 15:30:15
后面,我们在进行时间/日期大小比较时,使用
date_format
函数转换为标准格式后,再进行比较,避免出现类似上述的问题。
这个问题会比较隐蔽,不注意就出现问题了,还是养成
时间比较就先转换为标准格式
的习惯吧。