BigQuery SQL使用TRIM()函数格式化时间间隔输出格式问题

你之前使用TRIM()出错的核心原因是：BigQuery的TRIM(string, characters)函数会删除字符串首尾所有属于指定字符集的字符，而非删除固定前缀字符串。你指定的字符集包含0，因此小时位的前导0也会被误删，才会出现丢失小时位的问题。

这个方案不需要处理时间差转字符串后的冗余内容，还能完美支持跨天行程，同时直接得到可用于统计计算的数值字段。

1. 核心逻辑 #

使用DATETIME_DIFF（如果你的started_at/ended_at是TIMESTAMP类型则改用TIMESTAMP_DIFF）直接计算行程的总秒数，后续不管是格式化输出还是计算平均、最大、最小时长，都可以直接基于这个数值处理。

2. 完整查询代码 #

SELECT 
  ride_id, 
  rideable_type, 
  started_at, 
  ended_at,
  -- 计算行程总秒数，正数为正常时长，负数代表结束时间早于开始时间的异常数据
  DATETIME_DIFF(ended_at, started_at, SECOND) AS duration_seconds,
  -- 将总秒数格式化为 HH:MM:SS 格式，自动累加跨天行程的小时数
  CONCAT(
    LPAD(CAST(FLOOR(duration_seconds / 3600) AS STRING), 2, '0'), ':',
    LPAD(CAST(FLOOR((duration_seconds % 3600) / 60) AS STRING), 2, '0'), ':',
    LPAD(CAST(duration_seconds % 60 AS STRING), 2, '0')
  ) AS duration_format,
  EXTRACT(TIME FROM started_at) AS start_time,
  EXTRACT(TIME FROM ended_at) AS end_time,
  EXTRACT(DAYOFWEEK FROM started_at) AS day_of_the_week
FROM `first-project-325905.bike_2020_april.july_bike`
WHERE member_casual = 'member'

3. 时长统计示例 #

直接使用duration_seconds字段即可完成统计需求，示例代码：

SELECT
  -- 平均时长，单位：秒
  AVG(duration_seconds) AS avg_duration_seconds,
  -- 最大时长，单位：秒
  MAX(duration_seconds) AS max_duration_seconds,
  -- 最小时长，单位：秒
  MIN(duration_seconds) AS min_duration_seconds
FROM (
  SELECT DATETIME_DIFF(ended_at, started_at, SECOND) AS duration_seconds
  FROM `first-project-325905.bike_2020_april.july_bike`
  WHERE member_casual = 'member'
)

如果需要把统计结果也格式化为HH:MM:SS，套用上面格式化部分的逻辑即可。

如果你不需要计算跨天行程的时长累加，只想把原有字符串的前缀删除，可以用REGEXP_REPLACE匹配固定前缀删除，避免误删前导0：

SELECT 
  ride_id, 
  rideable_type, 
  started_at, 
  ended_at,
  REGEXP_REPLACE(CAST((ended_at - started_at) AS STRING), r'^\d+-\d+ \d+ -', '') AS duration,
  EXTRACT(TIME FROM started_at) AS start_time,
  EXTRACT(TIME FROM ended_at) AS end_time,
  EXTRACT(DAYOFWEEK FROM started_at) AS day_of_the_week
FROM `first-project-325905.bike_2020_april.july_bike`
WHERE member_casual = 'member'

注意：该方案不会把天数转换成小时累加，仅适合当日行程使用，跨天行程会丢失天数对应的时长，优先推荐使用总秒数方案。

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