Hive查询转Snowflake时DATEDIFF日期计算返回0结果问题求助

核心错误点 #

• DATEDIFF参数顺序完全颠倒：Snowflake的DATEDIFF语法为DATEDIFF(时间单位, 较早的时间, 较晚的时间)，返回后者减前者的时间差。你的写法所有DATEDIFF的两个日期参数顺序完全写反，计算出的差值全部为负数，自然无法满足>=8的条件，最终导致返回0条结果。
• 时间转换逻辑错误：原Hive的unix_timestamp会按指定格式解析带时分秒的字符串为时间戳，计算的是满8*24小时的间隔。你直接用TO_DATE会丢弃时分秒，且没有指定输入的时间格式，大概率会出现日期解析错误甚至返回NULL。
• 过滤条件逻辑偏差：原逻辑是匹配2021-07-11 00:00:00及之后的所有时间，直接用TO_DATE仅匹配日期维度，虽不会直接导致无结果，但和原业务逻辑不一致。

修正后代码 #

AND (    
    -- 对应原逻辑：当前时间减fs创建时间间隔满8天
    (DATEDIFF(day, TO_TIMESTAMP(fs.Created_Date,'MM/DD/YYYY HH24:MI:SS'), CURRENT_TIMESTAMP()) >=8 AND st.mtcn IS NULL)
    -- 对应原逻辑：st创建时间减fs创建时间间隔满8天
    OR (DATEDIFF(day, TO_TIMESTAMP(fs.Created_Date,'MM/DD/YYYY HH24:MI:SS'), TO_TIMESTAMP(st.Created_Date,'MM/DD/YYYY HH24:MI:SS')) >=8)
    -- 对应原逻辑：st创建时间减mt交易时间间隔满8天
    OR (DATEDIFF(day, TO_TIMESTAMP(mt.opacket_transactiontime,'YYYY-MM-DD HH24:MI:SS'), TO_TIMESTAMP(st.Created_Date,'MM/DD/YYYY HH24:MI:SS')) >=8)
)
AND spc.spacket_disposition='A'
-- 对应原逻辑：mt交易时间大于等于2021-07-11 00:00:00
AND TO_TIMESTAMP(mt.OPacket_TransactionTime,'YYYY-MM-DD HH24:MI:SS') >= '2021-07-11 00:00:00'::TIMESTAMP

提示：如果需要完全对齐原Hive按秒计算满8整天的逻辑，把所有DATEDIFF的单位改为second，后面加上/86400 >=8即可。

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