HiveSQL下将YYYYMMDD格式数值转为日期并计算日期间差的求助

针对Hive环境的语法约束，可通过以下调整实现需求，无需创建临时表：

• 用cast(字段 as 类型)替换PostgreSQL专属的::类型转换语法
• 日期差值计算使用Hive内置datediff函数，保证日期运算兼容性
• 采用子查询嵌套逻辑规避Hive WHERE子句无法引用SELECT别名的限制

实现代码 #

SELECT 
    statement_date,
    paid_date,
    Diff_in_days
FROM (
    SELECT 
        to_date(cast(statement_date_key AS string), 'yyyyMMdd') AS statement_date,
        to_date(cast(paid_date_key AS string), 'yyyyMMdd') AS paid_date,
        datediff(
            to_date(cast(statement_date_key AS string), 'yyyyMMdd'),
            to_date(cast(paid_date_key AS string), 'yyyyMMdd')
        ) AS Diff_in_days
    FROM `Table`
) t
WHERE Diff_in_days >= 90;

如果希望简化重复的日期转换逻辑，也可以用Hive支持的CTE写法，同样不会创建物理临时表：

WITH date_convert AS (
    SELECT 
        to_date(cast(statement_date_key AS string), 'yyyyMMdd') AS statement_date,
        to_date(cast(paid_date_key AS string), 'yyyyMMdd') AS paid_date
    FROM `Table`
)
SELECT 
    statement_date,
    paid_date,
    datediff(statement_date, paid_date) AS Diff_in_days
FROM date_convert
WHERE datediff(statement_date, paid_date) >= 90;

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