Snowflake - .toISOString() incorrectly rounds timestamp in JS stored procedure

Question

I've encountered a weird issue in Snowflake when using .toISOString() within a JavaScript stored procedure. The behaviour is very inconsistent and I'm suspecting this might actually be a bug.

In some cases, when timestamp passed as a stored procedure argument is converted with .toISOString(), it looks like it's unreasonably rounded down by 0.001. Examples provided below.

When I call .toISOString() in JS itself, this does not seem to happen at all.

Has someone encountered a similar issue? We are going to truncate the timestamps to seconds in our solution to ensure that it's working correctly but that's not something very reliable long-term.

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)); 
-- returns: 2024-05-16T10:41:07.569Z

CALL "timestamp_test" ('2024-05-16 10:41:07.578'::TIMESTAMP_NTZ(9));
-- returns: 2024-05-16T10:41:07.577Z

CALL "timestamp_test" ('2024-05-16 10:41:07.577'::TIMESTAMP_NTZ(9));
-- returns: 2024-05-16T10:41:07.577Z

CALL "timestamp_test" ('2024-05-16 10:41:07.56'::TIMESTAMP_NTZ(9)); 
-- returns: 2024-05-16T10:41:07.560Z

Answer 1

This happen because Snowflake's TIMESTAMP_NTZ has a precision of up to nanoseconds but JavaScript's Date object has a precision of only milliseconds, and This discrepancy can lead to unexpected rounding behaviour when converting timestamps

A solution could be to manually construct the ISO string in the stored procedure by truncating the timestamp to seconds, and ensuring that milliseconds are included explicitly

CREATE OR REPLACE PROCEDURE "timestamp_test"("ts" TIMESTAMP_NTZ(9))
RETURNS VARCHAR(16777216)
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS $$
    function toIsoStringWithPrecision(date) {
        const pad = (num, size) => ('000' + num).slice(size * -1);
        const isoString = date.getUTCFullYear() +
            '-' + pad(date.getUTCMonth() + 1, 2) +
            '-' + pad(date.getUTCDate(), 2) +
            'T' + pad(date.getUTCHours(), 2) +
            ':' + pad(date.getUTCMinutes(), 2) +
            ':' + pad(date.getUTCSeconds(), 2) +
            '.' + pad(date.getUTCMilliseconds(), 3) + 'Z';
        return isoString;
    }

    const date = new Date(ts.getTime());
    return toIsoStringWithPrecision(date);
$$;

pad here to ensure that each part of the date has the correct length ( by adding zeros if necessary )
getUTCMonth starts with 0 ( for example, Jan = 0, Fab = 1, ...etc ), that's why we add +1.