如何用Impala SQL计算当前时间与LAST_RESOLVED_DATE的时长（保留2位小数）

各位早上好：
我正在使用Impala SQL，现有字段LAST_RESOLVED_DATE，希望计算当前时间与该字段的小时数或分钟数（保留2位小数）。
当前转换字段的SQL语句如下：

from_unixtime(Cast(hpd_help_desk.LAST_RESOLVED_DATE AS BIGINT),'yyyy-MM-dd HH:mm:ss')   AS "INC_LAST_RESOLVE_DATE",

期望得到包含小时、分钟差的精确结果（示例结果略）。
我尝试用DATEDIFF函数实现，但得到的是整数结果，精度不足：

DATEDIFF(   NOW(),    
            TO_DATE(from_unixtime(Cast(hpd_help_desk.LAST_RESOLVED_DATE AS BIGINT),'yyyy-MM-dd HH:mm:ss')))*24 + hour(CURRENT_DATE()) - hour(TO_DATE(from_unixtime(Cast(hpd_help_desk.LAST_RESOLVED_DATE AS BIGINT),'yyyy-MM-dd HH:mm:ss'))
            ) AS "HOURS_SINCE_RESOLUTION",

尝试用UNIX_TIMESTAMP计算时出现语法错误：

(UNIX_TIMESTAMP(Cast(NOW())) - UNIX_TIMESTAMP(Cast(hpd_help_desk.LAST_RESOLVED_DATE)))                AS "MINUTES_SINCE_RESOLUTION",

错误信息如下：

ParseException: Syntax error in line 10:undefined: ...(UNIX_TIMESTAMP(Cast(NOW())) - UNIX_TIMESTAMP(Cast(hpd... ^ Encountered: ) Expected: AND, AS, BETWEEN, DIV, ILIKE, IN, IREGEXP, IS, LIKE, NOT, OR, REGEXP, RLIKE CAUSED BY: Exception: Syntax error

特此求助解决该问题，Peter

解决方案 #

1. 错误原因 #

你使用UNIX_TIMESTAMP时的写法存在问题：

• NOW()本身就是时间类型，无需用Cast()转换，Impala的UNIX_TIMESTAMP()函数可直接接收时间类型参数
• LAST_RESOLVED_DATE需要先转成BIGINT，再通过from_unixtime()转成标准时间类型，才能被UNIX_TIMESTAMP()正确解析

2. 精确小时差计算（保留2位小数） #

利用UNIX时间戳的差值计算：UNIX时间戳以秒为单位，差值除以3600得到小时数，再用ROUND()函数保留2位小数：

ROUND(
    (UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(from_unixtime(CAST(hpd_help_desk.LAST_RESOLVED_DATE AS BIGINT)))) / 3600,
    2
) AS "HOURS_SINCE_RESOLUTION"

3. 精确分钟差计算（保留2位小数） #

秒数差值除以60得到分钟数，同样用ROUND()控制精度：

ROUND(
    (UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(from_unixtime(CAST(hpd_help_desk.LAST_RESOLVED_DATE AS BIGINT)))) / 60,
    2
) AS "MINUTES_SINCE_RESOLUTION"

简化SQL的小技巧 #

无需重复转换LAST_RESOLVED_DATE，可以用以下两种方式简化SQL：

方法一：直接复用转换逻辑

SELECT
    from_unixtime(CAST(hpd_help_desk.LAST_RESOLVED_DATE AS BIGINT), 'yyyy-MM-dd HH:mm:ss') AS "INC_LAST_RESOLVE_DATE",
    ROUND((UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(from_unixtime(CAST(hpd_help_desk.LAST_RESOLVED_DATE AS BIGINT)))) / 3600, 2) AS "HOURS_SINCE_RESOLUTION",
    ROUND((UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(from_unixtime(CAST(hpd_help_desk.LAST_RESOLVED_DATE AS BIGINT)))) / 60, 2) AS "MINUTES_SINCE_RESOLUTION"
FROM hpd_help_desk;

方法二：使用WITH子句提前处理字段

WITH resolved_dates AS (
    SELECT
        from_unixtime(CAST(LAST_RESOLVED_DATE AS BIGINT), 'yyyy-MM-dd HH:mm:ss') AS INC_LAST_RESOLVE_DATE,
        UNIX_TIMESTAMP(from_unixtime(CAST(LAST_RESOLVED_DATE AS BIGINT))) AS resolved_unix
    FROM hpd_help_desk
)
SELECT
    INC_LAST_RESOLVE_DATE,
    ROUND((UNIX_TIMESTAMP(NOW()) - resolved_unix) / 3600, 2) AS HOURS_SINCE_RESOLUTION,
    ROUND((UNIX_TIMESTAMP(NOW()) - resolved_unix) / 60, 2) AS MINUTES_SINCE_RESOLUTION
FROM resolved_dates;

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