两个时间戳之间的MySql差异以秒为单位？

Is it possible to calculate difference between two timestamps in Mysql and get output result in seconds? like 2010-11-29 13:16:55 - 2010-11-29 13:13:55 should give 180 seconds.

是否可以计算Mysql中两个时间戳之间的差异，并在几秒钟内获得输出结果？喜欢2010-11-29 13:16:55 - 2010-11-29 13:13:55应该给180秒。

Thank you

谢谢

3 个解决方案

#1

Use the UNIX_TIMESTAMP function to convert the DATETIME into the value in seconds, starting from Jan 1st, 1970:

从1970年1月1日开始，使用UNIX_TIMESTAMP函数将DATETIME转换为以秒为单位的值：

SELECT UNIX_TIMESTAMP('2010-11-29 13:16:55') - UNIX_TIMESTAMP('2010-11-29 13:13:55') as output

Result:

结果：

output
-------
180

An easy way to deal with if you're not sure which value is bigger than the other -- use the ABS function:

如果您不确定哪个值大于另一个值，则可以轻松处理 - 使用ABS功能：

SELECT ABS(UNIX_TIMESTAMP(t.datetime_col1) - UNIX_TIMESTAMP(t.datetime_col2)) as output

#2

130

I do not think the accepted answer is a good universal solution!

我不认为接受的答案是一个很好的通用解决方案！

This is because the UNIX_TIMESTAMP() function fails for DATEs before 1970-01-01 (and for dates in the far future using 32 bit integers). This may happen easily for the day of birth of many living people.

这是因为UNIX_TIMESTAMP（）函数在1970-01-01之前的DATE失败（对于远期使用32位整数的日期）。这可能很容易发生在许多活着的人的出生那天。

A better solution is:

更好的解决方案是：

SELECT TIMESTAMPDIFF(SECOND, '2010-11-29 13:13:55', '2010-11-29 13:16:55')

Which can be modified to return DAY YEAR MONTH HOUR and MINUTE too!

哪些可以修改为返回DAY YEAR MONTH HOUR和MINUTE呢！

#3

TIMESTAMPDIFF method only works with datetime format. If you want the difference between just two times like '11:10:00' minus '10:20:00' then use

TIMESTAMPDIFF方法仅适用于datetime格式。如果您想要两次之间的差异，如'11：10：00'减去'10：20：00'那么请使用

select TIME_TO_SEC('11:10:00')-TIME_TO_SEC('10:20:00')

#1