mysql - 如何在几秒钟内获得两个时间戳之间的差异

Question

有没有一种方法可以在 MySQL 中进行查询，以秒为单位提供两个时间戳之间的差异，或者我需要在 PHP 中执行此操作吗？如果是这样，我该怎么做？

Answer 1

您可以使用 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)

您也可以使用 UNIX_TIMESTAMP()功能为 @Amber suggested在另一个答案中:

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 值是已经存储为一个整数，表示自纪元 (Source) 以来的秒数。引用 docs :

When UNIX_TIMESTAMP() is used on a TIMESTAMP column, the function returns the internal timestamp value directly, with no implicit “string-to-Unix-timestamp” conversion.

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)