date函数的在hive和mysql中的区别

引言：在Hive中我们截取日期通常使用substr(datetime,1,10)的方式，但在Mysql中其实还有一种date、month、year等的用法，直接取出日期，类似于substr的功能

一、hive中使用substr和date区别示例

1、用同样的方式对表进行日期分组求count

--使用substr
select
substr(tmtime,1,10) as dt,
count(1)
from data_version.device_z1_analysis_temporary
group by dt;

--使用date
select
date(tmtime) as dt,
count(1)
from data_version.device_z1_analysis_temporary
group by dt;

此时我们会发现结果有很大的区别，使用substr时确实做到了真正的按日期分组，而使用date时并没有真正的按日期分组，难道它压根就没有分组？

2、单独查一下2022-01-07看一下数据量

select
tmtime,
count(1)
from data_version.device_z1_analysis_temporary
where substr(tmtime,1,10) = '2022-01-07'
group by tmtime

这下就一目了然了，也就是说，date函数在Hive里只是展现出了截取日期的效果，实际上它底层依然是整个一串时间戳；换句话说，date函数只是从日期时间字符串中提取日期部分，并不会对日期进行格式转换。

3、刚刚是使用group by得出的结论，我们不妨用where在做一下验证

--where条件使用完整时间戳过滤
select date(tmtime)
from (select '2022-01-07 11:50:00' as tmtime)t
where date(tmtime) = '2022-01-07 11:50:00';

--where条件使用日期过滤
select date(tmtime)
from (select '2022-01-07 11:50:00' as tmtime)t
where date(tmtime) = '2022-01-07'

 结果也是显而易见，证明我们的结论是正确的

 二、Mysql中使用substr和date区别示例

1、用where在mysql中做一下对比

--where条件使用完整时间戳过滤
select date(tmtime)
from (select '2022-01-07 11:50:00' as tmtime)t
where date(tmtime) = '2022-01-07 11:50:00';

--where条件使用日期过滤
select date(tmtime)
from (select '2022-01-07 11:50:00' as tmtime)t
where date(tmtime) = '2022-01-07'

结果正好和Hive相反，这也说明了，mysql里的date真的就是和substr一个功能，不只是展示形式是日期，实际数据也是日期

 2、用substr验证一下，是否和date效果完全一致

select substr(tmtime,1,10)
from (select '2022-01-07 11:50:00' as tmtime)t
where substr(tmtime,1,10) = '2022-01-07';

 

 和date用法结果完全一致

三、总结

1、在Hive中，截取日期尽量用substr，因为date只是展现形式和substr一致，并不是数据本身

2、在mysql中，无所谓使用substr或date，效果完全一致