Snowflake中两种UTC格式字符串转Timestamp的方法咨询

问题说明 #

我有两种格式的时间字符串：'YYYY-MM-DDThh:mm:ssZ[UTC]' 和 'YYYY-MM-DDThh:mmZ[UTC]'。目前使用以下语句能正常完成转换：

TO_TIMESTAMP(SUBSTRING(timeStampString, 1, CHARINDEX('[UTC]',timeStampString) - 1)) AS myTimestamp

但尝试用COALESCE结合TRY_TO_TIMESTAMP的方式转换时失败了，语句如下：

coalesce(
    TRY_TO_TIMESTAMP(dateString, 'YYYY-MM-DDThh:mmZ[UTC]'),
    TRY_TO_TIMESTAMP(dateString, 'YYYY-MM-DDThh:mm:ssZ[UTC]')
) AS myTimestamp

解决方案 #

原因分析 #

失败的核心问题是TRY_TO_TIMESTAMP的格式参数中，方括号[]属于特殊转义字符，原格式里的[UTC]会被解析为字面量UTC，但原始字符串实际结尾是Z[UTC]（即Z加上带方括号的UTC），导致格式匹配失败。

可行方案 #

方案1：正确转义格式中的方括号

针对不同SQL方言，转义方括号的方式略有差异，以Snowflake为例，需要用双括号[[和]]来表示字面量的方括号，修改后的语句如下：

coalesce(
    TRY_TO_TIMESTAMP(dateString, 'YYYY-MM-DDThh:mmZ[[UTC]]'),
    TRY_TO_TIMESTAMP(dateString, 'YYYY-MM-DDThh:mm:ssZ[[UTC]]')
) AS myTimestamp

如果是其他SQL方言（如PostgreSQL），可能需要用反斜杠转义：Z\[UTC\]，具体可参考对应数据库的文档。

方案2：复用截取逻辑简化转换

直接沿用你原本的截取思路，先去掉末尾的[UTC]部分，再用TRY_TO_TIMESTAMP转换，这种方式无需处理复杂的格式转义，兼容性更好：

TRY_TO_TIMESTAMP(SUBSTRING(dateString, 1, CHARINDEX('[UTC]', dateString) - 1)) AS myTimestamp

截取后的字符串为YYYY-MM-DDThh:mm:ssZ或YYYY-MM-DDThh:mmZ，TRY_TO_TIMESTAMP可以自动识别这两种ISO格式的时间字符串。

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