如何在mysql中获取时间差

Question

我有两次start_time和end_time，我想得到start_time和end_time之间以秒为单位的差，这意味着如果start_time=1:1:0和end_time=1:2:3，那么我应该得到63。请告诉我应该向MySQL申请什么查询。

Answer 1

TIMESTAMPDIFF()会给你你想要的：

SELECT TIMESTAMPDIFF(SECOND, start_time, end_time) as timediff from `sometable` WHERE...

MySQL参考：http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_timestampdiff

Answer 2

我本可以评论这篇文章，但我只是加入了。在他的要求中，开始时间是过去的时间，但结果是一个正的时间差。所以

SELECT TIMESTAMPDIFF(SECOND, start_time, end_time)...

应该是

SELECT ABS(TIMESTAMPDIFF(SECOND, start_time, end_time))...;

完整示例

SET @startDate = NOW() - INTERVAL 63 SECOND;
SET @endDate = NOW();

SELECT ABS(TIMESTAMPDIFF(SECOND, @startDate, @endDate)) AS time_difference;
-- or this
SELECT ABS(TO_SECONDS(@startDate) - TO_SECONDS(@endDate)) AS time_difference;