如何处理BigQuery中类Google Analytics的特殊时间字符串？

我刚好处理过类似的需求！这种小时数可能是任意长度的时长字符串（比如85667:34:02、260:59:34），确实没法直接用BigQuery的常规TIME()函数——因为TIME类型仅支持0-23的小时数，超过范围就会报错。下面给你两个实用的解决方案，根据你的业务需求选择即可：

方案1：转换为总秒数（适合数值计算） #

如果需要对时长做求和、排序这类数值运算，把字符串转成总秒数是最直接的方式。通过SPLIT()拆分字符串，提取小时、分钟、秒后计算总和：

WITH sample_data AS (
  SELECT '85667:34:02' AS duration_str UNION ALL
  SELECT '260:59:34' UNION ALL
  SELECT '02:01:01'
)
SELECT
  duration_str,
  -- 拆分后计算总秒数，SAFE_CAST避免格式错误导致报错
  SAFE_CAST(SPLIT(duration_str, ':')[OFFSET(0)] AS INT64) * 3600 +
  SAFE_CAST(SPLIT(duration_str, ':')[OFFSET(1)] AS INT64) * 60 +
  SAFE_CAST(SPLIT(duration_str, ':')[OFFSET(2)] AS INT64) AS total_seconds
FROM sample_data

运行后会得到每个时长对应的总秒数，比如85667:34:02会转换成308407802秒。

方案2：转换为INTERVAL类型（适合时间加减运算） #

如果需要把时长和TIMESTAMP/DATE类型做加减（比如计算某个时间点加上该时长后的结果），可以把字符串转成BigQuery的INTERVAL类型：

WITH sample_data AS (
  SELECT '85667:34:02' AS duration_str UNION ALL
  SELECT '260:59:34' UNION ALL
  SELECT '02:01:01'
)
SELECT
  duration_str,
  -- 拼接成INTERVAL用于时间运算
  INTERVAL SAFE_CAST(SPLIT(duration_str, ':')[OFFSET(0)] AS INT64) HOUR +
  INTERVAL SAFE_CAST(SPLIT(duration_str, ':')[OFFSET(1)] AS INT64) MINUTE +
  INTERVAL SAFE_CAST(SPLIT(duration_str, ':')[OFFSET(2)] AS INT64) SECOND AS duration_interval,
  -- 示例：当前时间加上该时长
  CURRENT_TIMESTAMP() + duration_interval AS future_timestamp
FROM sample_data

这样得到的duration_interval可以直接和时间类型做运算，非常方便。

额外优化：格式合法性校验 #

如果担心输入字符串格式不规范（比如少了某一部分、分钟/秒不是两位数字），可以用正则表达式先做校验，避免无效计算：

WITH sample_data AS (
  SELECT '85667:34:02' AS duration_str UNION ALL
  SELECT '260:59:34' UNION ALL
  SELECT '02:01:01' UNION ALL
  SELECT 'invalid_format' -- 测试异常数据
)
SELECT
  duration_str,
  CASE
    WHEN REGEXP_CONTAINS(duration_str, r'^\d+:\d{2}:\d{2}$') THEN
      SAFE_CAST(SPLIT(duration_str, ':')[OFFSET(0)] AS INT64) * 3600 +
      SAFE_CAST(SPLIT(duration_str, ':')[OFFSET(1)] AS INT64) * 60 +
      SAFE_CAST(SPLIT(duration_str, ':')[OFFSET(2)] AS INT64)
    ELSE NULL -- 格式不合法时返回NULL
  END AS total_seconds
FROM sample_data

正则表达式^\d+:\d{2}:\d{2}$会检查字符串是否符合“任意数字:两位数字:两位数字”的格式，确保计算的准确性。

内容的提问来源于stack exchange，提问作者Martin Weitzmann