如何在Big Query中为元数据表的creation_time等列加小时并转Timestamp

针对元数据表metatables里的毫秒级时间戳字段creation_time和last_modified_time，可以通过以下步骤完成转换Timestamp并添加指定小时数的操作：

核心SQL示例（以添加10小时为例） #

SELECT
  -- 转换并调整creation_time
  TIMESTAMP_ADD(TIMESTAMP_SECONDS(creation_time / 1000), INTERVAL 10 HOUR) AS adjusted_creation_time,
  -- 转换并调整last_modified_time
  TIMESTAMP_ADD(TIMESTAMP_SECONDS(last_modified_time / 1000), INTERVAL 10 HOUR) AS adjusted_last_modified_time
FROM
  `你的项目ID.你的数据集ID.metatables`

代码说明 #

• 毫秒转秒：creation_time / 1000将毫秒级时间戳转换为秒级，因为BigQuery的TIMESTAMP_SECONDS()函数仅接收秒数作为输入。
• 转Timestamp类型：TIMESTAMP_SECONDS()函数把秒数格式化为标准的Timestamp类型。
• 添加指定小时：TIMESTAMP_ADD()函数给转换后的时间戳加上指定时长，这里用INTERVAL 10 HOUR表示添加10小时，可根据需求替换数值。

动态指定小时数的写法 #

如果需要灵活调整要添加的小时数，可以用变量声明：

DECLARE hours_to_add INT64 DEFAULT 10; -- 这里修改为你需要的小时数

SELECT
  TIMESTAMP_ADD(TIMESTAMP_SECONDS(creation_time / 1000), INTERVAL hours_to_add HOUR) AS adjusted_creation_time,
  TIMESTAMP_ADD(TIMESTAMP_SECONDS(last_modified_time / 1000), INTERVAL hours_to_add HOUR) AS adjusted_last_modified_time
FROM
  `你的项目ID.你的数据集ID.metatables`

特殊情况处理 #

如果creation_time或last_modified_time是字符串类型，需要先转换成数值型再处理，避免转换错误：

SELECT
  TIMESTAMP_ADD(TIMESTAMP_SECONDS(SAFE_CAST(creation_time AS INT64) / 1000), INTERVAL 10 HOUR) AS adjusted_creation_time
FROM
  `你的项目ID.你的数据集ID.metatables`

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