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

2022-01-13 00:00:00 timestamp mysql

有没有一种方法可以在 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 a TIMESTAMP column, 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)

相关文章