在BigQuery中将带AM/PM的字符串转换为Timestamp的问题

我的BigQuery表中有一个STRING字段存储时间值，格式混杂，示例如下：

Feb 27, 2021 14:11:25 PM    
Dec 07, 2020 04:48:44 AM    
Dec 07, 2020 04:48:35 AM    
Dec 07, 2020 04:48:55 AM    
Dec 07, 2020 04:51:51 AM

部分数据还存在不带AM/PM的24小时制格式（如Mar 16, 2021 22:17:27），尝试两种转换方法均失败，报错：

Failed to parse input string "Mar 16, 2021 22:17:27"

之前方法的问题 #

• 方法一：格式符使用错误，BigQuery中分钟对应%M、秒对应%S，而非%i和%s，导致解析逻辑失效。
• 方法二：无法兼容"24小时制时间+AM/PM"的错误格式（如14:11:25 PM），这类数据用12小时制解析会失败，且带AM/PM的字符串无法直接用24小时制格式解析。

正确解决方案 #

先统一清理字符串中的AM/PM标识，再用24小时制格式解析：

MIN(PARSE_TIMESTAMP('%b %d, %Y %H:%M:%S', TRIM(REGEXP_REPLACE(messagecreatedat, r'\s*(AM|PM)$', '')))) AS min_messagecreatedat

代码说明 #

1. REGEXP_REPLACE(messagecreatedat, r'\s*(AM|PM)$', '')：通过正则移除字符串末尾的AM/PM标识，\s*匹配标识前可能存在的空格。
2. TRIM()：清理字符串前后的多余空格，避免格式干扰。
3. PARSE_TIMESTAMP('%b %d, %Y %H:%M:%S', ...)：用正确的24小时制格式符解析处理后的字符串，兼容所有混杂格式。

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