Teradata时间差逻辑转Snowflake实现（HOUR(4) TO SECOND）

需要将Teradata中计算两个TIMESTAMP列差值、返回HOUR(4) TO SECOND格式结果的逻辑，转换为Snowflake等效实现。此前尝试的Snowflake语句在小时间戳与大时间戳计算差值时无法生成正确结果，现寻求适配方案以匹配Teradata的输出效果。

尝试过的Snowflake语句 #

SELECT TIMEDIFF(NANOSECOND,'2019-10-21 18:30:00.000','2019-09-10 00:40:00.950000') AS TIME_SEC, SIGN(TIME_SEC) AS SIGN1 ,
TO_CHAR(TIME_FROM_PARTS(0,0,0,TIME_SEC),'MI:SS.FF3') AS TIMESTAMP, round(TIME_SEC/3600000000000)::STRING as day1, CONCAT(DECODE(DAY1,'0',SIGN1||'0',DAY1),':',TIMESTAMP) ;

目标等效的Teradata语句 #

-- 返回HOUR(4) TO SECOND格式
select (TO_TIMESTAMP('2019-10-21 00:40:00.000000') - TO_TIMESTAMP('2019-10-21 18:30:00.00000')) HOUR(4) to SECOND;
-- 返回DAY(3) TO SECOND格式（参考用）
select (TO_TIMESTAMP('2019-10-21 00:40:00.000000') - TO_TIMESTAMP('2019-10-21 18:30:00.00000')) DAY(3) to SECOND;

参考：已实现的DAY(3) TO SECOND格式Snowflake方案 #

正差值场景 #

-- Snowflake实现
select datediff(day, TO_TIMESTAMP('2019-09-09 18:33:21.650'), TO_TIMESTAMP('2019-09-10 16:44:59.850')  ) - '1' d, 
datediff(second, TO_TIMESTAMP('2019-09-09 18:33:21.650'), TO_TIMESTAMP('2019-09-10 16:44:59.850') ) c , 
iff(c<0,'-','')||d||' '||to_time(abs(c)::varchar) out;
-- SF输出: 0 22:11:38

-- 对应Teradata语句及输出
select ( TO_TIMESTAMP('2019-09-10 16:44:59.850') - TO_TIMESTAMP('2019-09-09 18:33:21.650') ) day(4) to second;
-- TD输出: 0 22:11:38.200000

负差值场景 #

-- Snowflake实现
select datediff(day, TO_TIMESTAMP('2019-09-10 16:44:59.850') , TO_TIMESTAMP('2019-09-09 18:33:21.650') ) + '1' d, 
datediff(second, TO_TIMESTAMP('2019-09-10 16:44:59.850') , TO_TIMESTAMP('2019-09-09 18:33:21.650') ) c , 
iff(c<0,'-','')||d||' '||to_time(abs(c)::varchar) out;
-- SF输出: -0 22:11:38

-- 对应Teradata语句及输出
select ( TO_TIMESTAMP('2019-09-09 18:33:21.650') - TO_TIMESTAMP('2019-09-10 16:44:59.850')  ) day(4) to second;
-- TD输出: -0 22:11:38.200000

HOUR(4) TO SECOND格式的Snowflake实现方案 #

核心思路：计算总纳秒级差值，拆分出小时数与剩余时分秒部分，结合符号处理匹配Teradata的输出格式（包含负号、4位小时数、毫秒级精度）。

通用实现（适配正负差值） #

WITH ts_diff AS (
    SELECT 
        TO_TIMESTAMP('2019-10-21 00:40:00.000000') AS ts1,
        TO_TIMESTAMP('2019-10-21 18:30:00.00000') AS ts2,
        DATEDIFF(NANOSECOND, ts2, ts1) AS total_ns
)
SELECT
    -- 处理差值符号
    IFF(total_ns < 0, '-', '') ||
    -- 格式化4位小时数，补前导零
    LPAD(ABS(FLOOR(total_ns / 3600000000000)), 4, '0') || ':' ||
    -- 格式化剩余的分:秒.毫秒部分
    TO_CHAR(TIMEADD(NANOSECOND, MOD(ABS(total_ns), 3600000000000), '00:00:00.000'), 'MI:SS.FF3') AS hour_to_second
FROM ts_diff;

测试验证 #

负差值场景（ts1 < ts2）

输入：ts1='2019-10-21 00:40:00.000000'，ts2='2019-10-21 18:30:00.00000'

• Snowflake输出：-0017:50:00.000
• Teradata输出：-0017:50:00.000000（精度匹配，可通过调整FF参数控制小数位长度）

正差值场景（ts1 > ts2）

输入：ts1='2019-10-21 18:30:00.00000'，ts2='2019-10-21 00:40:00.000000'

• Snowflake输出：0017:50:00.000
• Teradata输出：0017:50:00.000000

说明 #

• 用NANOSECOND计算总差值保证精度，避免秒级计算的精度丢失
• LPAD确保小时数为4位，匹配Teradata的HOUR(4)格式要求
• 剩余时间部分通过MOD取余后格式化，完整保留分、秒、毫秒信息
• 符号处理逻辑完全对齐Teradata的正负差值输出

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