mysql - 时间戳差异并单独获取小时、分钟和秒

Question

所以我每行有 2 个表，我用它来获取平均时间(以分钟为单位)。

SELECT Sec_to_time (Avg (Timestampdiff (second, `survival`, `lastupdate`)))
FROM   main

但是我想单独获取小时、分钟和秒，这样我就可以在 php 中回显它们。

Answer 1

您可以使用MySQL HOUR(), MINUTE() and SECOND()函数将从 SEC_TO_TIME 返回的时间分解为其组件。

为了避免重复，您可以包装计算:

SELECT HOUR(x.timeDiff), MINUTE(x.timeDiff), SECOND(x.timeDiff) 
FROM 
  (
    SELECT Sec_to_time(Avg (Timestampdiff (second, `survival`, `lastupdate`))) as timeDiff
    FROM main
  ) AS x

SqlFiddle Here