递归CTE构建时间表后操作timestampntz列报错：数值超出可表示范围

问题描述 #

使用递归CTE构建日期时间表时，基础查询可正常执行，但对timestampntz列添加过滤条件后触发错误：

Number out of representable range: type FIXEDSB4{not null}, value 10

错误原因 #

递归生成hours和minutes时，Snowflake自动推断的整数类型（FIXEDSB4，4字节有符号整数）与timestamp_ntz_from_parts函数的参数类型不兼容，导致后续过滤操作出现范围溢出。

解决方案 #

方案1：明确指定递归CTE的整数类型

在生成小时、分钟的递归CTE中，显式将初始值转换为INT类型，避免自动推断的类型过小：

WITH RECURSIVE dates AS (
    SELECT CAST('2023-01-01' AS DATE) AS date
    UNION ALL
    SELECT date + 1
    FROM dates
    WHERE dates.date < CURRENT_DATE()
),
hours AS (
    SELECT CAST(0 AS INT) AS hour
    UNION ALL
    SELECT hour + 1
    FROM hours
    WHERE hours.hour < 23
),
minutes AS (
    SELECT CAST(0 AS INT) AS minute
    UNION ALL
    SELECT minute + 1
    FROM minutes
    WHERE minutes.minute < 59
),
datetimes AS (
    SELECT TIMESTAMP_NTZ_FROM_PARTS(
        YEAR(dates.date), MONTH(dates.date), DAY(dates.date),
        hours.hour, minutes.minute, 0
    ) AS dt 
    FROM dates
    CROSS JOIN hours
    CROSS JOIN minutes
)
SELECT *
FROM datetimes
WHERE dt >= '2023-06-01'::timestampntz;

方案2：使用更高效的时间序列生成方式

替换递归CTE，用GENERATOR函数结合DATEADD直接生成分钟级时间序列，性能更优且避免类型问题：

WITH datetimes AS (
    SELECT DATEADD(minute, seq4(), '2023-01-01'::timestamp_ntz) AS dt
    FROM TABLE(GENERATOR(ROWCOUNT => (DATEDIFF(minute, '2023-01-01', CURRENT_DATE()))))
)
SELECT *
FROM datetimes
WHERE dt >= '2023-06-01'::timestampntz;

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