Snowflake中含T字符的日期字符串转datetime报解析错误问题

报错原因 #

你之前的写法存在3个格式匹配错误：

• 格式串在日期和时间部分写了空格做分隔，未匹配原字符串里的T字面量分隔符
• 时间部分无小数秒，多余的.FF匹配符会触发解析失败
• 时区部分硬编码写+00，既无法匹配4位长度的时区偏移值（如+0000），也不兼容其他时区取值（如+0800、-0500）

你遇到的报错信息为：

Can't parse '2020-04-11T21:00:09+0000' as timestamp with format...

正确写法 #

你用的to_timestamp_ntz是Spark/Databricks SQL体系的函数，这类引擎默认原生支持ISO8601格式时间解析，最简单的写法可以省略格式串直接传值：

-- 转无时区时间戳
SELECT to_timestamp_ntz('2020-04-11T21:00:09+0000');

-- 转日期类型
SELECT to_date('2020-04-11T21:00:09+0000');

如果需要显式指定格式串做严格匹配，正确格式串如下，注意T作为字面量用双引号包裹，Z作为时区偏移匹配符匹配±HHMM格式的时区值：

-- 显式指定格式转无时区时间戳
SELECT to_timestamp_ntz('2020-04-11T21:00:09+0000', 'yyyy-MM-dd"T"HH:mm:ssZ');

-- 转带时区的时间戳类型
SELECT to_timestamp_tz('2020-04-11T21:00:09+0000', 'yyyy-MM-dd"T"HH:mm:ssZ');

-- 显式指定格式转日期类型
SELECT to_date('2020-04-11T21:00:09+0000', 'yyyy-MM-dd"T"HH:mm:ssZ');

注意事项 #

• 不要硬编码时区部分的固定值，否则遇到其他时区偏移的数据会直接解析失败
• 格式串里的非格式关键字字面量，稳妥起见用双引号包裹，避免被引擎识别为格式符触发解析错误
• 若时间字符串存在小数秒，可在秒的匹配符后加.FF对应，无小数秒时不要添加该匹配规则

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