Spark中将秒数值转换为仅时间格式（时长）的技术问询

直接将秒数cast为timestamp会把数值当作从UTC epoch（1970-01-01 00:00:00）开始的时间戳，再加上本地时区偏移，导致结果完全不符合时长需求。以下是两种可靠的解决思路：

方法1：手动计算时分秒并拼接字符串（最灵活） #

通过数学运算拆分小时、分钟、秒，按需拼接成想要的格式，完全不受时区影响：

示例：输出mm:ss或HH:mm:ss格式（自动判断是否显示小时） #

select 
  type, 
  count(*) as nombre, 
  max(date) as derniere_course, 
  round(avg(distance), 1) as distance, 
  avg(allure_moyenne) as allure_moyenne,
  -- 拼接时长字符串：秒数≥3600时显示小时，否则只显示分秒
  concat(
    case when avg(allure_moyenne) >= 3600 then concat(floor(avg(allure_moyenne)/3600), ':') else '' end,
    lpad(floor(mod(avg(allure_moyenne), 3600)/60), 2, '0'), ':',
    lpad(floor(mod(avg(allure_moyenne), 60)), 2, '0')
  ) as allure,
  round(avg(foulees_moy), 2) as foulees 
from activites
group by type
order by type

示例：保留小数秒（如mm:ss.SSSS） #

如果需要保留秒的小数部分，直接对剩余秒数做精度处理即可：

select 
  type, 
  count(*) as nombre, 
  max(date) as derniere_course, 
  round(avg(distance), 1) as distance, 
  avg(allure_moyenne) as allure_moyenne,
  concat(
    lpad(floor(avg(allure_moyenne)/60), 2, '0'), ':',
    lpad(round(mod(avg(allure_moyenne), 60), 4), 6, '0') -- 保留4位小数，补零到6位
  ) as allure,
  round(avg(foulees_moy), 2) as foulees 
from activites
group by type
order by type

方法2：利用from_unixtime+date_format指定时区 #

通过from_unixtime将秒数转换为UTC时区的时间戳，再提取时间部分，避免本地时区偏移：

select 
  type, 
  count(*) as nombre, 
  max(date) as derniere_course, 
  round(avg(distance), 1) as distance, 
  avg(allure_moyenne) as allure_moyenne,
  -- 指定UTC时区转换，再提取时间格式（可自定义：mm:ss/HH:mm:ss.SSS等）
  date_format(from_unixtime(avg(allure_moyenne), 'UTC'), 'mm:ss.SSS') as allure,
  round(avg(foulees_moy), 2) as foulees 
from activites
group by type
order by type

格式说明 #

• 'mm:ss'：仅显示分和秒（如07:32）
• 'HH:mm:ss'：显示时、分、秒（如01:07:32，适合秒数超过3600的场景）
• 'mm:ss.SSS'：保留三位小数秒（如07:32.094）

内容的提问来源于stack exchange，提问作者Marc Le Bihan