BigQuery中如何解析冒号分隔小数秒字符串为DATETIME/TIMESTAMP

待转换的时间字符串为'29-JUN-2022 05:56:31:653000000 AM'，由于BigQuery原生时间解析函数仅识别.作为秒与小数秒的分隔符，直接使用PARSE系列函数解析冒号分隔的场景会抛出格式不匹配错误，可通过以下两种方案实现转换：

方案1：定位替换分隔符后解析（性能最优） #

BigQuery的REPLACE函数支持指定替换第N次出现的目标字符，原时间字符串中一共存在3个冒号，前两个是时-分、分-秒的常规分隔符，第三个是秒与小数秒的异常分隔符，直接将第三个冒号替换为.即可走标准解析逻辑：

-- 转换为DATETIME类型
SELECT
  PARSE_DATETIME(
    '%d-%b-%Y %I:%M:%E*S %p',
    REPLACE('29-JUN-2022 05:56:31:653000000 AM', ':', '.', 3)
  ) AS res_datetime,
  -- 转换为TIMESTAMP类型，可按需指定时区，默认使用UTC
  PARSE_TIMESTAMP(
    '%d-%b-%Y %I:%M:%E*S %p',
    REPLACE('29-JUN-2022 05:56:31:653000000 AM', ':', '.', 3),
    'UTC'
  ) AS res_timestamp

解析结果：

• res_datetime：2022-06-29T05:56:31.653000
• res_timestamp：2022-06-29 05:56:31.653000+00

方案2：正则匹配替换分隔符（兼容性更强） #

如果待处理的时间字符串长度不固定、无法通过固定替换次数定位分隔符，可使用正则精准匹配秒位后的冒号做替换，适配更多格式变体：

SELECT
  PARSE_DATETIME(
    '%d-%b-%Y %I:%M:%E*S %p',
    REGEXP_REPLACE(
      '29-JUN-2022 05:56:31:653000000 AM',
      r'(\d{1,2}):(\d{6,9})( [AP]M)$',
      r'\1.\2\3'
    )
  ) AS res_datetime

解析说明 #

• 格式符中%d匹配两位日期、%b匹配三位缩写月份、%Y匹配四位年份、%I匹配12小时制小时、%M匹配分钟、%E*S自动适配带小数的秒值、%p匹配AM/PM标识，注意不要误用24小时制的%H格式符，否则会因AM/PM标识无法匹配报错。
• 原字符串的9位小数秒为纳秒精度，BigQuery时间类型最高支持微秒级精度，解析时会自动截断超出精度的部分，不影响结果准确性。

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