如何在BigQuery中处理含多格式时区的JS生成日期字符串

首先，你不需要自建时区对照表——字符串里的GMT±XXXX偏移量已经足够准确定位时间，括号里的时区别名（不管是英文、西班牙文还是其他语言）只是附加说明，不影响时间计算，直接把括号及内部内容去掉就行。

具体实现步骤 #

1. 清理日期字符串：用REGEXP_REPLACE去掉末尾的括号及里面的内容
2. 解析为Timestamp：用你原来的格式串解析清理后的字符串
3. 转换为Unix时间戳：用UNIX_SECONDS（秒级）或UNIX_MILLIS（毫秒级）转换

示例SQL：

WITH sample_data AS (
  SELECT 'Sat Aug 05 2023 15:15:04 GMT-0700 (PDT)' AS date_str UNION ALL
  SELECT 'Sat Aug 05 2023 11:22:54 GMT-0500 (Central Daylight Time)' UNION ALL
  SELECT 'Sat Aug 05 2023 15:04:36 GMT-0700 (hora de verano del Pacífico)'
)
SELECT
  date_str,
  -- 清理字符串：去掉括号及内部所有内容
  REGEXP_REPLACE(date_str, r'\s*\(.*\)', '') AS cleaned_date_str,
  -- 解析为Timestamp
  PARSE_TIMESTAMP("%a %b %d %Y %X GMT%z", REGEXP_REPLACE(date_str, r'\s*\(.*\)', '')) AS parsed_timestamp,
  -- 转换为Unix秒级时间戳
  UNIX_SECONDS(PARSE_TIMESTAMP("%a %b %d %Y %X GMT%z", REGEXP_REPLACE(date_str, r'\s*\(.*\)', ''))) AS unix_timestamp
FROM sample_data;

关于时区单独存储的问题 #

如果你的业务后续需要按时区维度做分析（比如统计不同时区的用户行为分布），把时区别名单独提取出来存储是合理的做法，可以用REGEXP_EXTRACT提取：

SELECT
  date_str,
  REGEXP_EXTRACT(date_str, r'\((.*)\)') AS timezone_name
FROM sample_data;

但如果只是为了转换Unix时间戳，完全不需要单独存储——因为Unix时间戳本身是UTC标准时间的计数，和时区无关。

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