如何在BigQuery中两列相减计算时间差并生成新列

你的started_at、ended_at为标准时间类型字段时，可直接通过BigQuery原生运算得到时间差，根据是否需要修改原表，分两种实现方式：

场景1：查询时动态生成时间差列（不修改原表） #

如果仅在取数分析时需要用到时间差，不需要改动原表结构和数据，直接在SELECT语句中计算即可：

• 直接相减会得到INTERVAL格式的时间间隔结果（格式为年-月 天 小时:分钟:秒）
• 如果需要拿到指定单位的数值结果（比如分钟数、秒数），使用对应时间差函数计算更方便

示例代码：

SELECT
  started_at,
  ended_at,
  -- 直接相减得到INTERVAL类型的原始时间差
  ended_at - started_at AS raw_duration,
  -- 计算整数分钟级差值，不足1分钟的部分自动截断
  TIMESTAMP_DIFF(ended_at, started_at, MINUTE) AS duration_minutes,
  -- 计算秒级差值
  TIMESTAMP_DIFF(ended_at, started_at, SECOND) AS duration_seconds
FROM
  `替换为你的项目ID.数据集名.表名`

注意：时间差函数的参数顺序固定为结束时间在前，开始时间在后，顺序写反会得到负数结果。如果你的字段是DATETIME类型而非TIMESTAMP类型，把代码里的TIMESTAMP_DIFF替换为DATETIME_DIFF即可，参数逻辑完全一致。

TIMESTAMP_DIFF/DATETIME_DIFF支持的常用时间单位参数：

• SECOND：秒
• MINUTE：分钟
• HOUR：小时
• DAY：天

用你提供的样例数据测试，第一行2020-01-21 20:06:59到2020-01-21 20:14:30的计算结果为：原始间隔0-0 0 0:7:31、分钟值7、秒值451，符合预期。

场景2：在原表新增持久化列，永久存储时间差值 #

如果需要把时间差固定存储在原表中，分两步操作：

1. 先给表新增对应类型的字段

-- 示例新增INT64类型的列存储分钟级差值，可根据需要调整字段类型和名称
ALTER TABLE `替换为你的项目ID.数据集名.表名`
ADD COLUMN ride_duration INT64;

2. 全表更新，用两列的时间差填充新字段

UPDATE `替换为你的项目ID.数据集名.表名`
SET ride_duration = TIMESTAMP_DIFF(ended_at, started_at, MINUTE)
WHERE TRUE; -- BigQuery全表更新必须加WHERE条件，传入TRUE代表更新所有行

内容的提问来源于stack exchange，提问作者H.C