如何获取当前日期和配置单元中存档的时间之间的天数?

brc7rcf0  于 2021-06-28  发布在  Hive
关注(0)|答案(1)|浏览(423)

mytable中有两个时间戳:
例如,“regdate” 20120719044326 '的格式为bigint;'subscr_date',例如' 2012-07-19 04:43:26 '的格式。。
我想把当前日期和regdate之间的天数作为reg\u天;当前日期和subscr\u日期之间的天数作为subscr\u天。。
那么如何在hive中编写查询呢?

p8ekf7hl

p8ekf7hl1#

如果regdate和subscr\u date是字符串:

select datediff(current_date,from_unixtime(unix_timestamp(unix_timestamp(regdate, 'yyyyMMddhhmmss'), 'yyyy-MM-dd')) as reg_days,
datediff(current_date,from_unixtime(unix_timestamp(subscr_date, 'yyyy-MM-dd hh:mm:ss'), 'yyyy-MM-dd')) as subscr_days
from mytable;

如果regdate是bigint,subscr\u date是字符串:

select datediff(current_date,from_unixtime(unix_timestamp(unix_timestamp(CAST(regdate AS STRING), 'yyyyMMddhhmmss'), 'yyyy-MM-dd')) as reg_days,
datediff(current_date,from_unixtime(unix_timestamp(subscr_date, 'yyyy-MM-dd hh:mm:ss'), 'yyyy-MM-dd')) as subscr_days
from mytable;

如果注册日期和子注册日期为2012-07-19,当前注册日期为2016-09-01,则结果为:
注册日1504子注册日1504

相关问题