php MySQL：如何以秒为单位获得两个时间戳之间的差异
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3528219/
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: how to get the difference between two timestamps in seconds
提问by The.Anti.9
Is there a way I can make a query in MySQL that will give me the difference between two timestamps in seconds, or would I need to do that in PHP? And if so, how would I go about doing that?
有没有一种方法可以在 MySQL 中进行查询，以秒为单位给出两个时间戳之间的差异，或者我需要在 PHP 中这样做吗？如果是这样，我将如何去做？
回答by Daniel Vassallo
You could use the TIMEDIFF()and the TIME_TO_SEC()functions as follows:
您可以按如下方式使用TIMEDIFF()和TIME_TO_SEC()函数：
SELECT TIME_TO_SEC(TIMEDIFF('2010-08-20 12:01:00', '2010-08-20 12:00:00')) diff;
+------+
| diff |
+------+
| 60 |
+------+
1 row in set (0.00 sec)
You could also use the UNIX_TIMESTAMP()function as @Amber suggestedin an other answer:
您还可以使用@Amber在其他答案中建议的UNIX_TIMESTAMP()功能：
SELECT UNIX_TIMESTAMP('2010-08-20 12:01:00') -
UNIX_TIMESTAMP('2010-08-20 12:00:00') diff;
+------+
| diff |
+------+
| 60 |
+------+
1 row in set (0.00 sec)
If you are using the TIMESTAMPdata type, I guess that the UNIX_TIMESTAMP()solution would be slightly faster, since TIMESTAMPvalues are already stored as an integer representing the number of seconds since the epoch (Source). Quoting the docs:
如果您使用的是TIMESTAMP数据类型，我想该UNIX_TIMESTAMP()解决方案会稍微快一点，因为TIMESTAMP值已经存储为一个整数，表示自纪元（Source）以来的秒数。引用文档：
When
UNIX_TIMESTAMP()is used on aTIMESTAMPcolumn, the function returns the internal timestamp value directly, with no implicit “string-to-Unix-timestamp” conversion.Keep in mind that
TIMEDIFF()return data type ofTIME.TIMEvalues may range from '-838:59:59' to '838:59:59' (roughly 34.96 days)
在列
UNIX_TIMESTAMP()上使用时TIMESTAMP，该函数直接返回内部时间戳值，没有隐式的“字符串到 Unix 时间戳”转换。请记住，
TIMEDIFF()返回数据类型TIME.TIME值的范围可能从“-838:59:59”到“838:59:59”（大约 34.96 天）
回答by David
How about "TIMESTAMPDIFF":
“TIMESTAMPDIFF”怎么样：
SELECT TIMESTAMPDIFF(SECOND,'2009-05-18','2009-07-29') from `post_statistics`
https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_timestampdiff
https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_timestampdiff
回答by Amber
UNIX_TIMESTAMP(ts1) - UNIX_TIMESTAMP(ts2)
If you want an unsigned difference, add an ABS()around the expression.
如果您想要无符号差异，请ABS()在表达式周围添加一个。
Alternatively, you can use TIMEDIFF(ts1, ts2)and then convert the time result to seconds with TIME_TO_SEC().
或者，您可以使用TIMEDIFF(ts1, ts2)，然后使用将时间结果转换为秒TIME_TO_SEC()。
回答by Power Engineering
Note that the TIMEDIFF()solution only works when the datetimesare less than 35 daysapart!
TIMEDIFF()returns a TIMEdatatype, and the max value for TIME is 838:59:59 hours (=34,96 days)
需要注意的是，TIMEDIFF()当溶液只能datetimes是小于35天分开！
TIMEDIFF()返回TIME数据类型，TIME的最大值为 838:59:59 小时（=34,96 天）
