Snowflake中如何转换并保留纳秒级小数的时间戳？

问题原因 #

Snowflake的TIMESTAMP系列类型（TIMESTAMP_NTZ、TIMESTAMP_LTZ、TIMESTAMP_TZ）默认精度为3位小数（即TIMESTAMP_NTZ(3)），即使转换时指定了FF9，存储或默认显示时会自动截断到3位。要保留9位小数，需要从列定义和转换函数两方面调整。

具体步骤 #

1. 创建/修改列时指定9位精度 #

确保目标列的类型是带9位小数的TIMESTAMP，例如：

-- 创建新表时指定精度
CREATE TABLE your_table (
    precise_ts TIMESTAMP_NTZ(9)
);

-- 修改现有列的精度
ALTER TABLE your_table MODIFY COLUMN precise_ts TIMESTAMP_NTZ(9);

可选类型说明：

• TIMESTAMP_NTZ(9)：不带时区的时间戳，适合将Z标识的UTC时间直接存入
• TIMESTAMP_TZ(9)：带时区的时间戳，会保留Z对应的UTC时区信息
• TIMESTAMP_LTZ(9)：本地时区时间戳，会自动转换为会话时区存储

2. 正确转换输入字符串 #

根据需求选择对应转换方式：

情况1：忽略Z标识，存储为不带时区的9位精度时间戳

INSERT INTO your_table (precise_ts)
VALUES (TO_TIMESTAMP_NTZ('2022-11-18T00:00:01.012345678Z', 'YYYY-MM-DD"T"HH24:MI:SS.FF9"Z"'));

情况2：保留时区信息，存储为带时区的9位精度时间戳

Snowflake支持自动识别ISO 8601格式的带时区字符串，直接转换即可：

INSERT INTO your_table (precise_ts)
VALUES (TO_TIMESTAMP_TZ('2022-11-18T00:00:01.012345678Z'));

也可以显式指定格式：

INSERT INTO your_table (precise_ts)
VALUES (TO_TIMESTAMP_TZ('2022-11-18T00:00:01.012345678Z', 'YYYY-MM-DD"T"HH24:MI:SS.FF9TZH:TZM'));

3. 验证完整精度的显示 #

默认查询结果可能只展示3位小数，但实际存储的是9位精度。可以用TO_VARCHAR强制输出完整格式：

SELECT TO_VARCHAR(precise_ts, 'YYYY-MM-DD"T"HH24:MI:SS.FF9') FROM your_table;

执行后会输出完整的9位小数时间：2022-11-18T00:00:01.012345678

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