时间戳差异，仅获取小时、分钟和秒

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

Answer 2

MySql中有hour()、minute()和second()函数。

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

Answer 3

尝试：

select concat(HOUR(Sec_to_time(Avg (Timestampdiff (second, login_datetime, logout_datetime)))),
  'hr ',
  MINUTE(Sec_to_time(Avg (Timestampdiff (second, login_datetime, logout_datetime)))),
  'min ',
  SECOND(Sec_to_time(Avg (Timestampdiff (second, login_datetime, logout_datetime)))), 
  'sec ') 
as his_time from table_name;