BigQuery如何计算两个TIMESTAMP列差值生成骑行时长列

核心问题排查点 #

你用的TIMESTAMP_DIFF函数本身语法是正确的，结果不符合预期基本是以下几个原因：

• 原始数据存在脏值：包含结束时间早于开始时间的错误记录、空值记录，这类记录计算出的负数/空值会和你预期的正时长不符
• 时区显示差导致的误判：TIMESTAMP类型在BigQuery中默认存储为UTC时间，控制台预览时会按你本地时区转换显示，如果你手动算时差的时候没对齐时区，就会觉得计算结果不对
• 操作逻辑错误：你之前执行的SELECT语句只是临时返回计算结果，不会把值写入你已经建好的ride_length字段

操作步骤 #

1. 先排查脏数据，执行以下语句确认异常记录占比：

SELECT
  COUNT(*) AS total_records,
  COUNTIF(started_at IS NULL OR ended_at IS NULL) AS null_time_records,
  COUNTIF(ended_at < started_at) AS reverse_time_records
FROM `cohesive-pad-345117.capstone_bike_data.trip_data`;

如果存在异常记录，建议先清洗这部分数据，再做时长计算。

2. 核对计算逻辑是否正确，执行以下语句取前20条记录手动校验：

SELECT
  started_at,
  ended_at,
  TIMESTAMP_DIFF(ended_at, started_at, SECOND) AS calc_ride_length
FROM `cohesive-pad-345117.capstone_bike_data.trip_data`
WHERE started_at IS NOT NULL AND ended_at IS NOT NULL AND ended_at >= started_at
LIMIT 20;

如果手动计算的秒数差和返回值一致，说明函数逻辑没有问题。

3. 把计算结果写入已建好的ride_length字段，执行UPDATE语句：

UPDATE `cohesive-pad-345117.capstone_bike_data.trip_data`
SET ride_length = TIMESTAMP_DIFF(ended_at, started_at, SECOND)
WHERE started_at IS NOT NULL AND ended_at IS NOT NULL AND ended_at >= started_at;

执行完成后ride_length字段就会存储对应记录的骑行秒数。如果需要存分钟/小时单位，把TIMESTAMP_DIFF的第三个参数换成MINUTE/HOUR即可。

注意：BigQuery的UPDATE语句要求必须加WHERE条件，否则无法执行，上述语句的WHERE条件同时过滤了会产生错误结果的脏数据，避免无效值写入。

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