Snowflake SQL中部分日期无法转换为Timestamp格式的问题排查

可能的核心原因 #

1. 隐藏字符干扰：JSON中存储的created_at字符串可能包含不可见的隐藏字符（如全角空格、换行符、制表符）或前后冗余空格，手动复制时这些字符会被忽略，但直接从JSON提取转换时会破坏格式匹配逻辑。
2. JSON字段类型异常：部分created_at字段可能并非字符串类型（如存储为数字、null或其他非字符串JSON类型），转成VARCHAR后生成不符合日期格式的内容。
3. 格式掩码的细微不匹配：虽然单数字月/日（如4/8而非04/08）理论上兼容mm/dd/yyyy掩码，但结合时区部分时，可能存在环境特定的兼容问题，或部分数据的时区格式存在变种（如+0000无冒号、带额外空格）。

具体排查步骤 #

1. 检测隐藏字符与冗余空格 #

通过以下查询查看异常数据的字符串长度、首尾字符ASCII码，判断是否存在隐藏字符：

SELECT 
    created_at,
    LENGTH(created_at) AS str_length,
    ASCII(SUBSTR(created_at, 1, 1)) AS first_char_ascii,
    ASCII(SUBSTR(created_at, -1, 1)) AS last_char_ascii
FROM (
    SELECT json_data:created_at::VARCHAR AS created_at
    FROM my_table
    WHERE TRY_TO_TIMESTAMP(json_data:created_at::VARCHAR, 'mm/dd/yyyy HH:MI:SS AM TZH:TZM') IS NULL 
      AND TRY_TO_TIMESTAMP(json_data:created_at::VARCHAR) IS NULL
) t

• 如果首尾ASCII码为32（半角空格），可尝试用TRIM清理后再转换：

  TRY_TO_TIMESTAMP(TRIM(json_data:created_at::VARCHAR), 'mm/dd/yyyy HH:MI:SS AM TZH:TZM')
  

• 如果存在非打印字符（如ASCII 10换行、9制表符），用正则表达式清理：

  TRY_TO_TIMESTAMP(REGEXP_REPLACE(json_data:created_at::VARCHAR, '[^[:print:]]', ''), 'mm/dd/yyyy HH:MI:SS AM TZH:TZM')
  

2. 验证JSON字段的原始类型 #

部分created_at可能不是字符串类型，导致转VARCHAR后格式异常：

SELECT 
    json_data:created_at::VARCHAR AS created_at_str,
    TYPEOF(json_data:created_at) AS original_type
FROM my_table
WHERE TRY_TO_TIMESTAMP(json_data:created_at::VARCHAR, 'mm/dd/yyyy HH:MI:SS AM TZH:TZM') IS NULL 
  AND TRY_TO_TIMESTAMP(json_data:created_at::VARCHAR) IS NULL

若存在非字符串类型（如数字、null），需针对这些类型单独处理（如过滤null、转换数字为合法日期字符串）。

3. 对比原始提取值与手动复制值的差异 #

取一条异常数据，直接提取转换和手动复制值转换对比：

-- 直接从JSON提取转换
SELECT TO_TIMESTAMP((SELECT json_data:created_at::VARCHAR FROM my_table WHERE ... LIMIT 1), 'mm/dd/yyyy HH:MI:SS AM TZH:TZM');

-- 手动复制值转换
SELECT TO_TIMESTAMP('4/8/2022 12:49:44 PM +00:00', 'mm/dd/yyyy HH:MI:SS AM TZH:TZM');

若前者失败后者成功，可通过逐字符对比ASCII码找出差异：

SELECT 
    SUBSTR(created_at, n, 1) AS char,
    ASCII(SUBSTR(created_at, n, 1)) AS char_ascii
FROM (
    SELECT json_data:created_at::VARCHAR AS created_at,
           GENERATE_SERIES(1, LENGTH(json_data:created_at::VARCHAR)) AS n
    FROM my_table WHERE ... LIMIT 1
) t

4. 检查时区格式变种 #

部分异常数据的时区可能不符合TZH:TZM格式（如+0000无冒号、+00无分钟部分），可尝试扩展掩码或调整正则匹配：

-- 兼容带/不带冒号的时区格式
TRY_TO_TIMESTAMP(json_data:created_at::VARCHAR, 'mm/dd/yyyy HH:MI:SS AM TZH:TZM')
TRY_TO_TIMESTAMP(json_data:created_at::VARCHAR, 'mm/dd/yyyy HH:MI:SS AM TZHTZM')

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