BigQuery字符串转TIME类型失败：合并数据集遇"0:00"无效时间字符串错误

问题出在"0:00"这种字符串不符合SQL标准TIME类型的格式要求——标准TIME格式需要小时部分为两位（如00:00），单数字小时会导致转换失败。以下是具体解决步骤：

临时转换（适合合并查询时直接处理） #

直接在查询中对字符串补全前导零后转换，无需修改原表结构：

SELECT
  -- 补全小时部分的前导零，再转换为TIME类型
  CAST(
    LPAD(SPLIT_PART(RideLength, ':', 1), 2, '0') || ':' || SPLIT_PART(RideLength, ':', 2)
    AS TIME
  ) AS RideLength,
  -- 其他需要的字段
FROM your_string_dataset;

永久修改表结构 #

如果需要将原STRING字段改为TIME类型，建议分步骤操作避免数据丢失：

1. 添加临时列存储转换后的值

ALTER TABLE your_string_dataset ADD COLUMN RideLength_temp TIME;

2. 更新临时列，处理字符串格式后转换

UPDATE your_string_dataset
SET RideLength_temp = CAST(
  LPAD(SPLIT_PART(RideLength, ':', 1), 2, '0') || ':' || SPLIT_PART(RideLength, ':', 2)
  AS TIME
);

3. 验证数据无误后，替换原列

ALTER TABLE your_string_dataset DROP COLUMN RideLength;
ALTER TABLE your_string_dataset RENAME COLUMN RideLength_temp TO RideLength;

兼容带秒的情况 #

如果部分RideLength字符串包含秒（如"1:23:45"），可以用更通用的逻辑处理：

SELECT
  CAST(
    LPAD(SPLIT_PART(RideLength, ':', 1), 2, '0') || ':' ||
    LPAD(SPLIT_PART(RideLength, ':', 2), 2, '0') ||
    CASE WHEN SPLIT_PART(RideLength, ':', 3) IS NOT NULL THEN ':' || LPAD(SPLIT_PART(RideLength, ':', 3), 2, '0') ELSE '' END
    AS TIME
  ) AS RideLength
FROM your_string_dataset;

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