如何在Snowflake中计算两个时间戳差值并按指定格式输出？

可以通过分步提取时间差的各个分量，再按格式拼接的方式实现需求。以下是具体的SQL实现方案：

实现思路 #

1. 分步计算两个时间戳之间的年、月、日、时、分、秒、毫秒差值，每一步计算后更新基准时间，确保后续分量计算基于剩余时间区间，避免跨周期误差。
2. 对月、日、时、分、秒补零至2位，毫秒补零至3位，最后将所有分量拼接成目标格式字符串。

示例SQL代码 #

WITH time_diff AS (
    SELECT 
        '2021-04-01 10:05:20.080'::TIMESTAMP_NTZ AS ts_start,
        '2022-05-02 20:10:30.090'::TIMESTAMP_NTZ AS ts_end,
        DATEDIFF(YEAR, ts_start, ts_end) AS year_diff,
        DATEADD(YEAR, DATEDIFF(YEAR, ts_start, ts_end), ts_start) AS after_year
),
month_calc AS (
    SELECT 
        *,
        DATEDIFF(MONTH, after_year, ts_end) AS month_diff,
        DATEADD(MONTH, DATEDIFF(MONTH, after_year, ts_end), after_year) AS after_month
    FROM time_diff
),
day_calc AS (
    SELECT 
        *,
        DATEDIFF(DAY, after_month, ts_end) AS day_diff,
        DATEADD(DAY, DATEDIFF(DAY, after_month, ts_end), after_month) AS after_day
    FROM month_calc
),
hour_calc AS (
    SELECT 
        *,
        DATEDIFF(HOUR, after_day, ts_end) AS hour_diff,
        DATEADD(HOUR, DATEDIFF(HOUR, after_day, ts_end), after_day) AS after_hour
    FROM day_calc
),
minute_calc AS (
    SELECT 
        *,
        DATEDIFF(MINUTE, after_hour, ts_end) AS minute_diff,
        DATEADD(MINUTE, DATEDIFF(MINUTE, after_hour, ts_end), after_hour) AS after_minute
    FROM hour_calc
),
second_calc AS (
    SELECT 
        *,
        DATEDIFF(SECOND, after_minute, ts_end) AS second_diff,
        DATEADD(SECOND, DATEDIFF(SECOND, after_minute, ts_end), after_minute) AS after_second
    FROM minute_calc
),
millisecond_calc AS (
    SELECT 
        *,
        DATEDIFF(MILLISECOND, after_second, ts_end) AS millisecond_diff
    FROM second_calc
)
SELECT 
    CONCAT(
        year_diff,
        LPAD(month_diff, 2, '0'),
        LPAD(day_diff, 2, '0'),
        LPAD(hour_diff, 2, '0'),
        LPAD(minute_diff, 2, '0'),
        LPAD(second_diff, 2, '0'),
        LPAD(millisecond_diff, 3, '0')
    ) AS formatted_diff
FROM millisecond_calc;

关键说明 #

• 使用TIMESTAMP_NTZ确保时间戳不带时区，避免时区转换影响计算结果；若需处理带时区的时间，需先统一转换为同一时区（如TIMESTAMP_TZ转TIMESTAMP_NTZ）。
• LPAD函数用于补零操作，确保每个分量符合格式要求：年为1位（无补零），月/日/时/分/秒为2位，毫秒为3位。
• 分步计算的方式避免了直接使用DATEDIFF获取多个分量时的精度误差（例如跨月天数差异导致的月差计算不准）。

内容的提问来源于stack exchange，提问作者Arvind Kumar Varma