Presto SQL如何将带T和时区的varchar日期字符串转为日期格式

报错原因 #

你之前的转换语句报错核心是格式串和原字符串结构不匹配：

• 原字符串日期段和时间段之间用大写字符T分隔，你写的格式串对应位置用了空格，解析到T的位置就直接匹配失败
• 原字符串末尾带+09:00格式的时区偏移，你之前的格式串要么没有对应匹配规则，要么前面分隔符不匹配导致根本走不到时区解析步骤

相关内容复现：

原字段值样例：

"2022-03-01T09:24:58+09:00"

之前使用的错误SQL：

date_parse(filename,  '%Y-%m-%d %T+09:00')
date_parse(ts,  '%Y-%m-%d %H:%i:%s')

抛出的报错信息：

Invalid format: "2022-03-01T09:24:58+09:00" is malformed at "T09:24:58+09:00"

正确转换方法 #

方法1：使用ISO 8601专用解析函数（推荐） #

你手上的时间字符串是标准ISO 8601带时区格式，Presto内置了专门的解析函数，不需要手写格式串，容错性最高：

-- 直接解析为带时区的timestamp类型
from_iso8601_timestamp(你的日期字段名)

常见后续处理写法：

• 转指定时区时间：叠加at time zone语法，比如转成UTC时区

  from_iso8601_timestamp(你的日期字段名) at time zone 'UTC'
  

• 只取日期部分：外层套date()函数

  date(from_iso8601_timestamp(你的日期字段名))
  

方法2：使用date_parse手写匹配格式 #

如果有自定义解析逻辑的需求，要保证格式串和原字符串每个字符严格对应，把中间的T作为字面量写入格式串，末尾的时区偏移用%z格式符匹配即可：

date_parse(你的日期字段名, '%Y-%m-%dT%H:%i:%s%z')

注意：date_parse对格式匹配要求极严格，输入字符串和格式串的字面量部分必须完全一致，哪怕多一个空格、错一个符号都会抛出格式错误。

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