hive时间差计算

有个小需求，计算商品发货时间和下单时间差，然后算出48小时内发货的占比

[mw_shl_code=sql,true]select sum(case when ((unix_timestamp(deliveredtime) - unix_timestamp(paytime)) / 3600) > 48
then 1 else 0
end) as deviled_countgt48hour,
sum(case when ((unix_timestamp(deliveredtime) - unix_timestamp(paytime)) / 3600) <= 48
then 1 else 0
end) as deviled_countlte48hour,
sum(case when ((unix_timestamp(deliveredtime) - unix_timestamp(paytime)) / 3600) is null
then 1 else 0
end) as deviledtimeisnull,
(sum(case when ((unix_timestamp(deliveredtime) - unix_timestamp(paytime)) / 3600) <= 48
then 1 else 0
end) / count(ordercodeofsys)) as rate_deviledlte48hour from table [/mw_shl_code]

hive sql已结很灵活了。(unix_timestamp(deliveredtime)这是转换为Linux时间吧，deliveredtime是hive time类型？

desehawk 发表于 2018-2-7 19:00
hive sql已结很灵活了。(unix_timestamp(deliveredtime)这是转换为Linux时间吧，deliveredtime是hive time ...

是的，time类型