如何在BigQuery中修正时间戳年份值（将0016改为2016）

问题场景 #

合并table1和table2时，table1提取的日期年份为2016，table2提取的日期年份错误显示为0016，导致合并后按date排序异常。两张原表的date_time列均为timestamp类型，使用BigQuery标准SQL处理。

解决方案 #

方案1：合并查询时直接修正日期（推荐） #

不需要单独执行UPDATE语句，直接在UNION ALL的查询过程中修正table2的日期年份，确保合并后的日期统一为2016开头：

SELECT
  id,
  EXTRACT(DATE FROM date_time) AS date,
  EXTRACT(TIME FROM date_time) AS time,
  WeightKg,
  WeightPounds,
  Fat,
  BMI,
  IsManualReport,
  LogId
FROM `projectname.fitness_data.weight_log_table1`
UNION ALL
SELECT
  Id,
  DATE_REPLACE(EXTRACT(DATE FROM date_time), YEAR, 2016) AS date,
  EXTRACT(TIME FROM date_time) AS time,
  WeightKg,
  WeightPounds,
  Fat,
  BMI,
  IsManualReport,
  LogId
FROM `projectname.fitness_data.weight_log_table2`
ORDER BY id, date, time

说明：

• 针对table2，使用DATE_REPLACE函数将提取出的日期年份强制替换为2016，确保与table1的日期格式一致。
• 此方法避免了后续更新操作，直接生成正确的合并结果。

方案2：若已生成合并表，执行UPDATE修正 #

如果已经将合并结果存入了新表（比如merged_weight_log），可以通过UPDATE语句批量修正年份错误的记录：

UPDATE `projectname.fitness_data.merged_weight_log`
SET date = DATE_REPLACE(date, YEAR, 2016)
WHERE EXTRACT(YEAR FROM date) = 16;

说明：

• 用WHERE条件筛选出年份为16（即显示为0016）的记录。
• 仅对错误记录执行年份替换，不影响其他正确数据。

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