Hive日期转换结果异常，求两YYYYMM值的月数差计算方法

一、日期转换异常的原因及修复 #

你写的转换语句里，unix_timestamp的格式参数用错了——Hive的日期格式规则中，大写MM代表月份，小写mm是分钟。你传入的'yyyymm'会让系统把字符串后两位（比如示例中的08）当成“分钟”解析，月份则默认补1，所以最终输出2019-01-01。

修复后的正确转换语句：

select 
  first_tran,
  cast(substr(first_tran,1,6) as int) as yyyymm,
  from_unixtime(unix_timestamp(substr(first_tran,1,6), 'yyyyMM')) as month_start_date
from t.table;

这样示例值20190804提取的201908会被正确转为2019-08-01。

二、计算两个YYYYMM值的月数差的简便方法 #

有两种实用方案，按需选择：

方案1：用Hive内置日期函数months_between #

先把两个YYYYMM值转为当月第一天的日期，再用函数计算差值：

select 
  from_unixtime(unix_timestamp(substr(first_tran,1,6), 'yyyyMM')) as first_month,
  from_unixtime(unix_timestamp('202308', 'yyyyMM')) as target_month,
  months_between(
    from_unixtime(unix_timestamp('202308', 'yyyyMM')), 
    from_unixtime(unix_timestamp(substr(first_tran,1,6), 'yyyyMM'))
  ) as month_diff
from t.table;

函数会返回精确的月数差，若为整月则输出整数。

方案2：直接数值运算（性能更优） #

利用YYYYMM的整数格式特性，拆分年份和月份计算，无需日期转换，适合大数据量场景：

select 
  cast(substr(first_tran,1,6) as int) as yyyymm,
  -- 公式：(目标年份-原年份)*12 + (目标月份-原月份)
  ((2023 - cast(substr(first_tran,1,4) as int)) * 12) + (8 - cast(substr(first_tran,5,2) as int)) as month_diff
from t.table;

或更简洁的写法：

select 
  cast(substr(first_tran,1,6) as int) as yyyymm,
  -- 把YYYYMM转成“年份*12+月份”的数值后做差
  (2023*12 + 8) - (cast(substr(first_tran,1,4) as int)*12 + cast(substr(first_tran,5,2) as int)) as month_diff
from t.table;

内容的提问来源于stack exchange，提问作者Maths12