MySQL:如何在秒内获得两次时间戳的区别。

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中这样做吗?如果是的话，我该怎么做呢?

4 个解决方案

#1

117

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 suggested in an other answer:

您还可以使用UNIX_TIMESTAMP()函数，如@Amber在另一个答案中建议的那样:

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 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()解决方案会稍微快一些，因为时间戳值已经存储为一个整数，表示从历元(源)开始的秒数。引用文件:

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.

当在TIMESTAMP列上使用UNIX_TIMESTAMP()时，函数将直接返回内部时间戳值，并没有隐式的“string-to-Unix-timestamp”转换。

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)

请记住，TIMEDIFF()返回时间的数据类型。时间值可以从“-838:59:59”到“838:59:59”(大约34.96天)

#2

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

#3

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()将时间结果转换为秒。

#4

Note that the TIMEDIFF() solution only works when the datetimes are less than 35 days apart! TIMEDIFF() returns a TIME datatype, and the max value for TIME is 838:59:59 hours (=34,96 days)

注意，TIMEDIFF()解决方案只在日期间隔小于35天时才有效!TIMEDIFF()返回一个时间数据类型，时间的最大值为838:59:59小时(=34,96天)

#1

117