问题描述
有没有一种方法可以在 MySQL 中进行查询，以秒为单位提供两个时间戳之间的差异，或者我需要在 PHP 中执行此操作吗?如果是这样，我该怎么做?
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?
推荐答案
你可以使用 TIMEDIFF() 和 TIME_TO_SEC() 函数如下:
You could use the TIMEDIFF() and the TIME_TO_SEC() functions as follows:
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)
您也可以使用 UNIX_TIMESTAMP() 功能为 @Amber 建议在另一个答案中:
You could also use the UNIX_TIMESTAMP() function as @Amber suggested in an other answer:
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)
如果您使用 TIMESTAMP 数据类型，我猜 UNIX_TIMESTAMP() 解决方案会稍微快一些，因为 TIMESTAMP 值是已经存储为一个整数，表示自纪元以来的秒数 (来源).引用 docs:
If you are using the TIMESTAMP data type, I guess that the UNIX_TIMESTAMP() solution would be slightly faster, since TIMESTAMP values are already stored as an integer representing the number of seconds since the epoch (Source). Quoting the docs:
当 UNIX_TIMESTAMP() 用于 TIMESTAMP 列时，函数直接返回内部时间戳值，没有隐式的字符串到 Unix 时间戳"转换.
When
UNIX_TIMESTAMP()is used on aTIMESTAMPcolumn, the function returns the internal timestamp value directly, with no implicit "string-to-Unix-timestamp" conversion.
请记住 TIMEDIFF() 返回TIME的数据类型.TIME 值的范围可以从 '-838:59:59' 到 '838:59:59'(大约 34.96 天)
Keep in mind that TIMEDIFF() return data type of TIME. TIME values may range from '-838:59:59' to '838:59:59' (roughly 34.96 days)
这篇关于MySQL:如何在几秒钟内获得两个时间戳之间的差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！