BigQuery中如何从拆分的日期/时间/时区生成UTC时间戳

更优方案：无需字符串拼接 #

直接通过日期、时间函数组合构造本地时间，再转换为UTC timestamp，避免字符串拼接的繁琐和潜在格式问题：

SELECT
  TIMESTAMP(
    DATETIME(
      DATE(YEAR, MONTH, DAY),
      PARSE_TIME("%H%M", FORMAT("%04d", local_time))
    ),
    timezone
  ) AS utc_timestamp
FROM your_table

逻辑说明： #

1. DATE(YEAR, MONTH, DAY)：直接用年、月、日字段构造日期类型
2. FORMAT("%04d", local_time)：将数字类型的local_time（如1930、930）转为4位字符串（如'1930'、'0930'），确保小时部分补零
3. PARSE_TIME("%H%M", ...)：将4位字符串解析为TIME类型
4. DATETIME(...)：组合日期和时间，得到本地时区的DATETIME
5. TIMESTAMP(datetime, timezone)：将指定时区的DATETIME转换为UTC时区的TIMESTAMP

拼接字符串方案 #

如果偏好字符串拼接的方式，可按以下步骤实现：

SELECT
  PARSE_TIMESTAMP(
    "%Y-%m-%d %H:%M",
    FORMAT("%d-%02d-%02d %02d:%02d", YEAR, MONTH, DAY, DIV(local_time, 100), MOD(local_time, 100)),
    timezone
  ) AS utc_timestamp
FROM your_table

逻辑说明： #

1. FORMAT(...)：将年、月、日、本地时间拆分为小时（DIV(local_time, 100)）和分钟（MOD(local_time, 100)），拼接成YYYY-MM-DD HH:MM格式的字符串
2. PARSE_TIMESTAMP(format, string, timezone)：指定本地时区，将字符串解析为UTC时区的TIMESTAMP

注意事项： #

• 确保timezone字段的值为BigQuery支持的时区格式，比如Asia/Shanghai、UTC+8，避免无效时区导致解析错误
• 若local_time本身是字符串类型（如'1930'），可直接使用PARSE_TIME("%H%M", local_time)，无需FORMAT("%04d", ...)处理

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