BigQuery中如何将秒数转换为x days, hh:mm:ss格式？

直接通过拆分时长各部分再格式化拼接，就能得到你要的格式，完美避开之前遇到的小数、进位、格式冗余问题：

SELECT
  ride_id,
  started_at,
  ended_at,
  ride_len_sec,
  -- 拼接成目标格式
  FORMAT("%d days, %02d:%02d:%02d",
    days,
    hours,
    minutes,
    seconds
  ) AS ride_duration
FROM (
  SELECT
    ride_id,
    started_at,
    ended_at,
    ride_len_sec,
    -- 计算完整天数
    ride_len_sec // 86400 AS days,
    -- 拆分剩余秒数中的小时
    MOD(ride_len_sec, 86400) // 3600 AS hours,
    -- 拆分剩余秒数中的分钟
    MOD(MOD(ride_len_sec, 86400), 3600) // 60 AS minutes,
    -- 最后剩下的秒数
    MOD(MOD(ride_len_sec, 86400), 60) AS seconds
  FROM (
    SELECT
      ride_id,
      started_at,
      ended_at,
      TIMESTAMP_DIFF(ended_at, started_at, SECOND) AS ride_len_sec
    FROM `dataset.data`
  )
)

核心细节说明： #

• 用整数除法（//）和取余（MOD）拆分时长，完全避免小数问题，比如2100秒会准确拆成0天、0小时、35分钟、0秒
• %02d是格式化占位符，确保小时、分钟、秒不足两位时自动补零，满足hh:mm:ss的格式要求
• 这个方法支持任意时长，不管行程是几小时还是几天，都能正确输出

针对你提到的其他问题的补充： #

1. CAST转int的问题：你之前直接对总小时数转int会进位，是因为没有先取余拆分——必须先把超过整小时的部分剥离，再取整，才能得到正确的小时数
2. TIME函数的限制：没错，TIME函数仅支持0-23小时的范围，超过24小时会自动取模，完全不适合表示长行程时长
3. MAKE_INTERVAL的冗余值：如果非要用这个函数，可以用正则替换去掉多余的年、月、日前缀，但这个方法依赖BigQuery的输出格式，稳定性不如直接计算：

SELECT
  ride_len_sec,
  REGEXP_REPLACE(
    FORMAT("%t", MAKE_INTERVAL(second => ride_len_sec)),
    r'^\d+-\d+ \d+ ',
    ''
  ) AS cleaned_duration
FROM (
  SELECT
    ride_id,
    TIMESTAMP_DIFF(ended_at, started_at, SECOND) AS ride_len_sec
  FROM `dataset.data`
)

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