如何将Snowflake中LIST命令输出的last_modified转为时间戳？

要解决LIST命令输出的时间戳无法直接转换的问题，核心是使用带格式修饰符的时区时间戳转换函数，同时兼容个位数日期和GMT时区：

步骤1：执行LIST命令 #

先获取目标stage的文件列表：

LIST @your_target_stage;

步骤2：解析并转换时间戳 #

通过RESULT_SCAN获取LIST的输出结果，使用TO_TIMESTAMP_TZ结合匹配格式字符串完成转换：

SELECT
    name AS file_name,
    size AS file_size_bytes,
    -- 核心转换逻辑：兼容个位数日期和GMT时区
    TO_TIMESTAMP_TZ(last_modified, 'DY, FMDD MON YYYY HH24:MI:SS "GMT"') AS last_modified_timestamp
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));

关键格式说明 #

• FMDD：FM修饰符会自动去除日期的前导空格，完美适配"4 Mar"这类个位数日期场景
• HH24：确保24小时制的小时解析正确（避免12:31:50这类时间被误判为AM/PM）
• "GMT"：用双引号固定匹配输出中的时区字符串，避免解析时将其视为变量部分

扩展用法：时区转换与日期筛选 #

如果需要转换到其他时区，或者按日期过滤文件，可直接基于转换后的时间戳操作：

SELECT
    name AS file_name,
    CONVERT_TIMEZONE('Asia/Shanghai', TO_TIMESTAMP_TZ(last_modified, 'DY, FMDD MON YYYY HH24:MI:SS "GMT"')) AS shanghai_last_modified,
    size AS file_size_bytes
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
-- 筛选2025年3月10日之后修改的文件
WHERE TO_TIMESTAMP_TZ(last_modified, 'DY, FMDD MON YYYY HH24:MI:SS "GMT"') >= '2025-03-10 00:00:00 GMT';

这个方案完全适配LIST命令输出的时间戳格式，无需临时修改字符串，可稳定用于批量文件的日期筛选和排序场景。

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