QuickSight对接Athena从文件路径解析时间戳返回NULL问题

你的SQL返回null是以下几个错误叠加导致的：

• 正则匹配规则错误：\w仅匹配字母、数字、下划线，无法覆盖时间串里的-、空格、:、.分隔符；同时微秒部分写的.w{6}未给w加转义、小数点也属于未转义的通配符，根本匹配不到目标时间片段，regexp_extract直接返回null，后续所有嵌套函数的计算结果都为null。
• 类型转换逻辑冗余且有风险：直接将字符串cast为timestamp属于隐式转换，依赖Athena的默认格式识别，一旦字符串格式和默认预期不符就会返回null；额外加的date_format步骤是把timestamp转回字符串，再套date()函数又把字符串转成日期，多了两次无意义的类型转换，很容易触发格式不匹配问题。
• 时间格式符错误：Athena基于Trino/Presto引擎，date_format函数里SSS才代表毫秒，你写的SSSSSS属于无效格式符，会导致格式化失败。

正确可用的SQL写法 #

date(
  AT_TIMEZONE(
    date_parse(
      regexp_extract("$path", '(\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}\.\d{6})'),
      '%Y-%m-%d %H:%i:%s.%f'
    ),
    'America/Los_Angeles'
  )
) AS time_stamp

写法说明 #

• 正则部分全部用\d匹配数字，给小数点加转义符\.，可以精准提取到文件名里2022-06-02 19:26:48.491730这段标准时间串，自动过滤后面的无关后缀。
• 用date_parse显式指定时间解析格式，替代隐式cast，从根源避免格式识别错误：格式符和引擎语法一一对应，%Y为4位年、%m为2位月、%d为2位日、%H为24小时制小时、%i为分钟、%s为秒、%f为微秒。
• 去掉冗余的date_format转换步骤，解析得到原生timestamp类型后直接做时区转换，最后调用date()截取日期值即可，减少不必要的类型转换风险。

调试时建议从最内层函数开始逐层执行，先确认regexp_extract返回的时间字符串正确，再逐层叠加后续转换逻辑，方便快速定位问题。

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