Snowflake中TO_TIMESTAMP转换时间字符串出现未识别错误如何解决

你的写法存在两处问题：

• 直接执行col_name::VARCHAR不会自动去掉值外层的双引号，也无法处理字段实际存储为JSON结构时间对象的情况
• 从报错信息可以判断，你目标列存储的不是你列举的ISO格式时间字符串，而是序列化后的Java OffsetDateTime格式的JSON结构，所以直接调用TO_TIMESTAMP无法识别

分两种场景处理：

场景1：列存储的确实是带双引号的ISO时间字符串（即你示例里的"2018-03-08T22:43:23.848Z"格式） #

先去掉外层双引号再转时间即可，代码如下：

TO_TIMESTAMP(TRIM(col_name::VARCHAR, '"'))

用TRIM函数指定去掉首尾的双引号后再做时间转换即可正常运行。

场景2：列存储的是报错里的JSON结构时间对象 #

先解析JSON取出对应字段，再拼接成合法时间格式转换，代码如下：

TO_TIMESTAMP(
  CONCAT(
    col_name:year::STRING, '-',
    LPAD(col_name:monthValue::STRING, 2, '0'), '-',
    LPAD(col_name:dayOfMonth::STRING, 2, '0'), 'T',
    LPAD(col_name:hour::STRING, 2, '0'), ':',
    LPAD(col_name:minute::STRING, 2, '0'), ':',
    LPAD(col_name:second::STRING, 2, '0'), '.',
    col_name:nano::STRING / 1000000, 'Z'
  )
)

通过Snowflake原生的JSON字段访问语法，取出年、月、日、时、分、秒、纳秒等字段，拼接成标准ISO时间格式后再做转换即可。

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