如何在BigQuery中用timestamp_diff实现跨天时分格式时间差？

你当前用timestamp_diff计算两个时间戳的分钟差，想要格式化为HH:mm样式，但跨天计算时结果错误——比如计算2023-01-03 17:00:00 UTC和2023-01-01 15:10:00 UTC的时间差，之前的方法只返回01:50，漏掉了跨天的48小时。

问题出在之前的方案只处理了单日的小时差，没有把跨天的天数转换成小时数累加进去。正确的做法是先算出总分钟差，再拆解成总小时数和剩余分钟数，最后补零拼接：

示例SQL代码 #

-- 计算总分钟差，再格式化为HH:mm（支持跨天）
WITH time_calc AS (
  SELECT 
    timestamp_diff('2023-01-03 17:00:00 UTC', '2023-01-01 15:10:00 UTC', minute) AS total_minutes
)
SELECT
  CONCAT(
    LPAD(FLOOR(total_minutes / 60), 2, '0'),  -- 总小时数，补零到2位
    ':',
    LPAD(total_minutes % 60, 2, '0')         -- 剩余分钟数，补零到2位
  ) AS formatted_time_diff
FROM time_calc;

这段代码会返回正确的49:50（2天48小时 + 1小时50分钟）。如果是单日的情况，比如2023-01-01 17:00:00 UTC和2023-01-01 15:10:00 UTC，会返回01:50，符合需求。

核心逻辑说明 #

1. 用timestamp_diff算出两个时间的总分钟差，这一步能准确统计跨天的总时长
2. 用FLOOR(total_minutes / 60)得到总小时数（包含跨天的所有小时）
3. 用total_minutes % 60得到剩余的分钟数
4. 用LPAD函数把小时和分钟补零到2位，再用CONCAT拼接成HH:mm格式

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