在Google BigQuery中如何转换超过24小时的字符串时间？

由于BigQuery的TIME类型仅支持00:00:00至23:59:59的范围，无法直接存储超过24小时的时长，以下是几种可行的替代方案，既能保留时长语义，又支持排序、过滤等数据分析操作：

方案1：转换为总秒数（数值类型） #

将HH:MM:SS格式的字符串拆分为小时、分钟、秒，计算总秒数。数值类型天然支持排序、比较，是最直接的解决方案：

SELECT
  duration,
  -- 计算总秒数，兼容所有合法的HH:MM:SS格式
  (SAFE_CAST(SPLIT(duration, ':')[OFFSET(0)] AS INT64) * 3600) +
  (SAFE_CAST(SPLIT(duration, ':')[OFFSET(1)] AS INT64) * 60) +
  SAFE_CAST(SPLIT(duration, ':')[OFFSET(2)] AS INT64) AS total_seconds
FROM Run_Time

• 使用场景：需要进行时长统计、排序、范围过滤（如WHERE total_seconds > 86400筛选超过24小时的记录）。

方案2：转换为INTERVAL类型 #

BigQuery支持INTERVAL类型存储时长，可直接用于比较和排序，同时保留时长的语义：

SELECT
  duration,
  INTERVAL 
    SAFE_CAST(SPLIT(duration, ':')[OFFSET(0)] AS INT64) HOUR +
    SAFE_CAST(SPLIT(duration, ':')[OFFSET(1)] AS INT64) MINUTE +
    SAFE_CAST(SPLIT(duration, ':')[OFFSET(2)] AS INT64) SECOND AS duration_interval
FROM Run_Time

• 使用场景：需要直观的时长比较（如WHERE duration_interval > INTERVAL '24' HOUR），或与时间字段进行加减运算。

方案3：转换为结构化STRUCT类型 #

将时长拆分为小时、分钟、秒存储在STRUCT中，便于单独提取各部分进行分析，同时支持多字段排序：

SELECT
  duration,
  STRUCT(
    SAFE_CAST(SPLIT(duration, ':')[OFFSET(0)] AS INT64) AS hours,
    SAFE_CAST(SPLIT(duration, ':')[OFFSET(1)] AS INT64) AS minutes,
    SAFE_CAST(SPLIT(duration, ':')[OFFSET(2)] AS INT64) AS seconds
  ) AS duration_struct
FROM Run_Time

• 排序示例：ORDER BY duration_struct.hours, duration_struct.minutes, duration_struct.seconds
• 使用场景：需要单独分析小时、分钟、秒维度的数据，或需要结构化展示时长信息。

方案4：转换为总小时数（带小数） #

如果更关注以小时为单位的时长统计，可转换为带小数的总小时数：

SELECT
  duration,
  (SAFE_CAST(SPLIT(duration, ':')[OFFSET(0)] AS NUMERIC) +
   SAFE_CAST(SPLIT(duration, ':')[OFFSET(1)] AS NUMERIC)/60 +
   SAFE_CAST(SPLIT(duration, ':')[OFFSET(2)] AS NUMERIC)/3600) AS total_hours
FROM Run_Time

• 使用场景：需要按小时维度进行聚合分析，或与其他小时单位的指标对比。

所有方案均使用SAFE_CAST避免非法格式导致的报错，确保10万条记录都能被正确处理，不会出现NULL值（前提是原duration字段均为合法的HH:MM:SS格式）。

内容的提问来源于stack exchange，提问作者Richard Liu