MySql 的两个时间戳之间的差异（以秒为单位）？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4300380/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySql difference between two timestamps in Seconds?
提问by Good Guy
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
谢谢
采纳答案by OMG Ponies
Use the UNIX_TIMESTAMP functionto convert the DATETIME into the value in seconds, starting from Jan 1st, 1970:
使用UNIX_TIMESTAMP 函数将 DATETIME 转换为以秒为单位的值，从 1970 年 1 月 1 日开始：
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
回答by OderWat
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！
回答by Santhosh Gandhe
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 方法仅适用于日期时间格式。如果你想要'11:10:00'减去'10:20:00'这样的两次之间的差异，那么使用
select TIME_TO_SEC('11:10:00')-TIME_TO_SEC('10:20:00')
