HIVE计算月份差

hive:
经过测试：
select months_between(‘2020-04‘,‘2019-09‘) from mid.dual --null
select months_between(‘2020-04-02‘,‘2019-09-02‘) from mid.dual --7
select months_between(‘2020-04-02‘,‘2019-09-20‘) from mid.dual --6.41935484
select months_between(‘2020-04-20‘,‘2019-09-02‘) from mid.dual --7.58064516


select unix_timestamp(substr(20200408,1,6),‘yyyyMM‘) from mid.dual --1585670400
select from_unixtime(unix_timestamp(substr(20200409,1,6),‘yyyyMM‘),‘yyyy-MM-dd‘) from mid.dual    --2020-04-01
最终：
select months_between(from_unixtime(unix_timestamp(substr(20200409,1,6),‘yyyyMM‘),‘yyyy-MM-dd‘),
from_unixtime(unix_timestamp(substr(20190922,1,6),‘yyyyMM‘),‘yyyy-MM-dd‘)) 
from mid.dual    --7

oracle:
select to_date(‘202004‘,‘YYYYMM‘) from dual --2020/4/1
select months_between(to_date(‘20200402‘,‘YYYYMMdd‘),to_date(‘20190920‘,‘YYYYMMdd‘)) from dual --6.41935483870968
select months_between(to_date(‘20200420‘,‘YYYYMMdd‘),to_date(‘20190902‘,‘YYYYMMdd‘)) from dual --7.58064516129032
select months_between(to_date(‘202004‘,‘YYYYMM‘),to_date(‘201909‘,‘YYYYMM‘)) from dual --7