如何在BigQuery中将无补零可变长度日期字符串转换为Timestamp

核心报错原因 #

你遇到的解析失败通常是以下三个问题导致的：

• 日期和月份的格式符顺序写反：示例输入是月/日/年的顺序，如果误用%d/%m/%Y格式符，14会被识别为月份超出合法范围，触发报错
• 混用了24小时制格式符%H和AM/PM标识：12小时制的时间搭配AM/PM时，必须用格式符%I（12小时制），不能用%H（24小时制）
• 部分输入的AM/PM标识不完整（如示例中的P而非PM），不符合%p格式符的匹配规则

具体实现方案 #

BigQuery的数字格式符默认自动兼容有/无前导零的输入，无需额外对个位数的月、日、小时做补零处理，按照以下场景选择对应方案即可：

1. 标准场景（AM/PM标识完整） #

直接使用正确的格式符调用PARSE_TIMESTAMP即可：

SELECT PARSE_TIMESTAMP("%m/%d/%Y %I:%M:%S %p", "4/14/2016 8:00:00 PM") AS parsed_ts

返回结果为：2016-04-14 20:00:00 UTC

2. 兼容AM/PM缺字场景 #

针对5/7/2016 5:00:00 P这类AM/PM仅保留首字母的输入，先通过正则替换补全AM/PM标识后再解析即可：

SELECT 
  PARSE_TIMESTAMP(
    "%m/%d/%Y %I:%M:%S %p",
    REGEXP_REPLACE("5/7/2016 5:00:00 P", r' ([AP])$', r' \1M')
  ) AS parsed_ts

返回结果为：2016-05-07 17:00:00 UTC

3. 转DateTime类型 #

如果需要转DateTime而非Timestamp，把函数替换为PARSE_DATETIME即可，格式符规则完全一致：

SELECT 
  PARSE_DATETIME(
    "%m/%d/%Y %I:%M:%S %p",
    REGEXP_REPLACE(你的日期字段名, r' ([AP])$', r' \1M')
  ) AS parsed_datetime
FROM 你的表名

常用格式符说明 #

• %m：匹配月份，支持1-12（无前导零）、01-12（有前导零）
• %d：匹配日期，支持1-31、01-31
• %Y：匹配4位年份
• %I：匹配12小时制小时，支持1-12、01-12
• %M：匹配分钟
• %S：匹配秒
• %p：匹配AM/PM标识，不区分大小写

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