Snowflake中JS存储过程.toISOString()时间戳错误舍入问题

在Snowflake的JavaScript存储过程中调用.toISOString()时，部分传入的TIMESTAMP_NTZ(9)类型时间戳会出现不合理的0.001毫秒向下舍入问题，而原生JavaScript环境中无此异常。目前临时通过截断到秒保证功能，但该方案丢失精度，并非长期可靠解。

复现代码 #

CREATE OR REPLACE PROCEDURE "timestamp_test"("ts" TIMESTAMP_NTZ(9))
RETURNS VARCHAR(16777216)
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS $$
    return ts.toISOString();
    // return new Date(ts).toISOString();
$$;

CALL "timestamp_test" ('2024-05-16 10:41:07.57'::TIMESTAMP_NTZ(9)); 
-- 返回异常结果: 2024-05-16T10:41:07.569Z

CALL "timestamp_test" ('2024-05-16 10:41:07.578'::TIMESTAMP_NTZ(9));
-- 返回异常结果: 2024-05-16T10:41:07.577Z

CALL "timestamp_test" ('2024-05-16 10:41:07.577'::TIMESTAMP_NTZ(9));
-- 返回正确结果: 2024-05-16T10:41:07.577Z

CALL "timestamp_test" ('2024-05-16 10:41:07.56'::TIMESTAMP_NTZ(9)); 
-- 返回正确结果: 2024-05-16T10:41:07.560Z

问题原因 #

Snowflake的TIMESTAMP_NTZ(9)支持纳秒精度，但JavaScript的Date对象基于二进制浮点数实现，仅能精确表示部分十进制毫秒值。当Snowflake将纳秒级时间戳映射到JavaScript对象时，部分十进制毫秒值会因浮点精度丢失被近似为略小的值，最终导致.toISOString()输出时向下舍入。

解决方案 #

方案1：使用Snowflake内置SQL函数格式化 #

绕过JavaScript的精度问题，直接调用Snowflake的TO_VARCHAR函数完成ISO字符串转换：

CREATE OR REPLACE PROCEDURE "timestamp_test"("ts" TIMESTAMP_NTZ(9))
RETURNS VARCHAR(16777216)
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS $$
    const stmt = snowflake.execute({
        sqlText: "SELECT TO_VARCHAR(?, 'YYYY-MM-DD\"T\"HH24:MI:SS.FF3\"Z\"')",
        binds: [ts]
    });
    stmt.next();
    return stmt.getColumnValue(1);
$$;

该方案利用Snowflake自身的时间格式化逻辑，完全避免JavaScript的浮点精度问题，结果准确可靠。

方案2：手动构造ISO字符串（JavaScript层面处理） #

通过Snowflake时间对象的原生方法提取纳秒数，手动计算并拼接毫秒部分，绕过.toISOString()：

CREATE OR REPLACE PROCEDURE "timestamp_test"("ts" TIMESTAMP_NTZ(9))
RETURNS VARCHAR(16777216)
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS $$
    // 提取时间各分量
    const year = ts.getFullYear();
    const month = String(ts.getMonth() + 1).padStart(2, '0');
    const day = String(ts.getDate()).padStart(2, '0');
    const hours = String(ts.getHours()).padStart(2, '0');
    const minutes = String(ts.getMinutes()).padStart(2, '0');
    const seconds = String(ts.getSeconds()).padStart(2, '0');
    
    // 纳秒转毫秒并四舍五入，避免浮点误差
    const millis = Math.round(ts.getNano() / 1000000);
    const millisStr = String(millis).padStart(3, '0');
    
    return `${year}-${month}-${day}T${hours}:${minutes}:${seconds}.${millisStr}Z`;
$$;

此方案通过直接操作纳秒精度的原始值，确保毫秒部分计算准确，输出符合ISO格式的字符串。

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