BigQuery中转换MM/DD/YYYY格式字符串日期为标准时间戳求助

方法1：创建包含转换后日期的新表（推荐免费版用户） #

由于免费版BigQuery无法使用UPDATE类DML语句，直接生成结构正确的新表替代原表是最稳妥的方式：

CREATE OR REPLACE TABLE `dataproject.bellabeat_fitness_data.412_512_heart_cleaned`
AS
SELECT
  Id,
  PARSE_TIMESTAMP('%m/%d/%Y %r', Time) AS Time, -- 直接生成UTC时区的timestamp类型
  Calories
FROM `dataproject.bellabeat_fitness_data.412_512_heart`

执行后，新表的Time列就是你需要的YYYY-MM-DD HH:MM:SS UTC格式，可直接用于后续分析。

方法2：创建视图实时转换（无需存储新表） #

如果不想额外存储新表，可创建视图实现实时格式转换，查询时直接调用视图即可：

CREATE OR REPLACE VIEW `dataproject.bellabeat_fitness_data.412_512_heart_view`
AS
SELECT
  Id,
  CAST(PARSE_DATETIME('%m/%d/%Y %r', Time) AS TIMESTAMP) AS Time,
  Calories
FROM `dataproject.bellabeat_fitness_data.412_512_heart`

你之前遇到的错误原因解析 #

• 追加列报错Invalid schema update. Cannot add fields (field: f0_)：保存查询结果时未指定列名，BigQuery自动生成了默认列名f0_，与原表schema不匹配导致失败。需明确指定列名后再写入，或直接创建新表。
• 子查询报错scalar subquery produces more than one element：你的UPDATE子查询未关联当前行的Id，返回了所有行的结果，无法匹配单一行。即使是付费版，正确的UPDATE语句应为：

UPDATE `dataproject.bellabeat_fitness_data.412_512_heart`
SET Time = PARSE_TIMESTAMP('%m/%d/%Y %r', Time)
WHERE TRUE

额外提示 #

• 若存在少数格式异常的日期字符串，可添加SAFE_前缀避免查询中断：SAFE_PARSE_TIMESTAMP('%m/%d/%Y %r', Time)，异常值会返回NULL，后续可单独筛选处理。
• 后续上传数据时，可在BigQuery上传界面的「Schema」步骤，将Time列类型设为TIMESTAMP，再在「Advanced options」中填写格式字符串%m/%d/%Y %r，BigQuery会自动完成格式转换，无需后续处理。

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