如何在BigQuery中正确计算跨天骑行行程的时间戳差值？

方法1：判断时间顺序补全天数 #

如果数据中存在end_time早于start_time的情况（比如跨午夜的行程记录顺序异常），可以通过判断时间先后，对早于起始时间的结束时间补加1天再计算差值：

SELECT 
  IF(
    end_time < start_time,
    TIMESTAMP_DIFF(TIMESTAMP_ADD(end_time, INTERVAL 1 DAY), start_time, HOUR),
    TIMESTAMP_DIFF(end_time, start_time, HOUR)
  ) AS duration
FROM fast-audio-430317-q9.citibike_trips;

方法2：直接取时间差绝对值 #

如果所有负数结果都是由跨天或时间顺序颠倒导致的，直接用绝对值函数简化计算：

SELECT ABS(TIMESTAMP_DIFF(end_time, start_time, HOUR)) AS duration
FROM fast-audio-430317-q9.citibike_trips;

方法3：基于UNIX秒数计算 #

将时间戳转换为UNIX秒数，通过数学运算确保结果为正时长，适合处理所有跨天场景：

SELECT 
  (UNIX_SECONDS(end_time) - UNIX_SECONDS(start_time) + 86400) % 86400 / 3600 AS duration_hours
FROM fast-audio-430317-q9.citibike_trips;

逻辑说明：先计算秒级差值，加上1天的秒数（86400）后取模86400，确保结果落在0-86400秒区间，最后除以3600转换为小时。比如23:00到次日6:00的秒差为-64800，加86400后得到21600，除以3600正好是7小时。

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