如何在BigQuery标准SQL中解析带时区的指定格式日期字符串

我懂你现在的困扰——这种包含星期、月份、日期、时间、时区偏移和时区名称的字符串，直接用PARSE_DATE确实搞不定，毕竟PARSE_DATE只负责处理纯日期部分，没法覆盖后面的时间和时区信息。其实我们可以用**PARSE_TIMESTAMP**来完整解析整个字符串，再根据需求转成DATE类型就好。

直接可用的SQL示例 #

SELECT
  -- 解析成带时区的时间戳
  PARSE_TIMESTAMP("%a %b %d %Y %T GMT%z (%Z)", "Mon Sep 11 2017 00:00:00 GMT+0200 (CEST)") AS parsed_timestamp,
  -- 转成日期类型（默认使用会话时区）
  DATE(PARSE_TIMESTAMP("%a %b %d %Y %T GMT%z (%Z)", "Mon Sep 11 2017 00:00:00 GMT+0200 (CEST)")) AS parsed_date,
  -- 若需要指定时区转换日期（比如转成巴黎时区的日期）
  DATE(PARSE_TIMESTAMP("%a %b %d %Y %T GMT%z (%Z)", "Mon Sep 11 2017 00:00:00 GMT+0200 (CEST)"), "Europe/Paris") AS paris_timezone_date

格式符详细说明 #

我给你拆解一下用到的格式符，方便你理解和调整：

• %a：匹配缩写的星期名称（比如Mon）
• %b：匹配缩写的月份名称（比如Sep）
• %d：匹配两位数的日期（这里你之前用了%j，那是一年中的第几天，和你的字符串格式不符，所以要改成%d）
• %Y：匹配4位数的年份（比如2017）
• %T：匹配24小时制的时间格式HH:MM:SS（对应字符串里的00:00:00）
• GMT%z：匹配时区偏移部分，%z会识别+0200这种带符号的偏移格式，前面的GMT要和字符串里的前缀对应上
• (%Z)：匹配括号里的时区缩写（比如(CEST)），%Z会识别这类时区标识

补充说明 #

如果你的字段是动态的（不是固定字符串），只需要把示例里的字符串替换成你的字段名就行，比如：

SELECT
  DATE(PARSE_TIMESTAMP("%a %b %d %Y %T GMT%z (%Z)", your_date_column)) AS parsed_date
FROM
  your_dataset.your_table

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