BigQuery中如何将新计算的INTERVAL列转换为TIME格式？

一、INTERVAL转TIME的正确方法 #

BigQuery里无法直接用CAST(trip_length AS TIME)转换INTERVAL类型，得通过提取时分秒再构造TIME：

TIME(
  EXTRACT(HOUR FROM trip_length),
  EXTRACT(MINUTE FROM trip_length),
  EXTRACT(SECOND FROM trip_length)
) AS trip_length_time

注意：TIME类型只能表示一天内的时间范围（00:00:00到23:59:59），如果你的trip_length超过24小时，转换后会自动截断成当天的时分秒（比如25小时会变成1小时）。如果需要保留完整时长（如25:30:00），建议转成字符串格式：

FORMAT("%02d:%02d:%02d",
  EXTRACT(HOUR FROM trip_length) + EXTRACT(DAY FROM trip_length)*24,
  EXTRACT(MINUTE FROM trip_length),
  EXTRACT(SECOND FROM trip_length)
) AS trip_length_full_str

二、在原查询中直接实现转换 #

把转换逻辑直接嵌入原查询的SELECT语句即可，不用单独处理结果集：

SELECT
  started_at,
  ended_at,
  ended_at - started_at AS trip_length, -- 保留原INTERVAL列
  -- 直接生成TIME类型列
  TIME(
    EXTRACT(HOUR FROM ended_at - started_at),
    EXTRACT(MINUTE FROM ended_at - started_at),
    EXTRACT(SECOND FROM ended_at - started_at)
  ) AS trip_length_time
FROM
  `your-project.your-dataset.your-table`
-- 加上你的筛选条件
WHERE
  started_at >= '2024-01-01'

三、创建临时表用于项目 #

根据需求选择不同类型的临时表：

1. 会话级临时表（当前会话有效） #

前缀加#，会话结束后自动删除，适合临时存储处理后的数据：

CREATE TEMP TABLE #processed_trips AS
SELECT
  started_at,
  ended_at,
  TIME(
    EXTRACT(HOUR FROM ended_at - started_at),
    EXTRACT(MINUTE FROM ended_at - started_at),
    EXTRACT(SECOND FROM ended_at - started_at)
  ) AS trip_length_time
FROM
  `your-project.your-dataset.your-table`
WHERE
  -- 筛选条件

后续在同一个会话里可以直接查询#processed_trips。

2. 查询内临时复用（CTE） #

如果只是在单个查询内复用处理逻辑，用WITH子句创建公共表达式（CTE）更高效：

WITH processed_trips AS (
  SELECT
    started_at,
    ended_at,
    TIME(
      EXTRACT(HOUR FROM ended_at - started_at),
      EXTRACT(MINUTE FROM ended_at - started_at),
      EXTRACT(SECOND FROM ended_at - started_at)
    ) AS trip_length_time
  FROM
    `your-project.your-dataset.your-table`
)
-- 后续查询直接使用CTE
SELECT *
FROM processed_trips
WHERE trip_length_time > TIME('00:15:00')

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