mysql数据库获取执行时间差,mysql-是否有一条语句来计算数据库中读数之间的时间差？...

From a quick google search:

The MysqL TIMEDIFF() function returns the difference between two time or datetime values. The way it works is,you provide the two values to compare,and TIMEDIFF() subtracts the second value from the first,then returns the result as a time value.

编辑：

在重新考虑了问题之后,看来您的任务似乎比乍看起来要复杂得多.如果要检查两个RFID存取之间的差异,则要困难得多.如果您要查询一次和另一次之间的所有记录,则难度要小得多.您的第一个示例：

SELECT COUNT(*) FROM tag_logs

WHERE tag_logs = tag_no

BETWEEN timestamp ('timestamp','timestamp');

只会将两个时间戳记的值相加,这将导致距unix纪元两倍的日期成为现在的日期；并且由于您没有为between关键字提供第二个值,因此将其选择为00​​:00:00(1970年1月1日,0个时点),并且将返回每条记录.

您的第二个示例：

SELECT *

FROM tag_logs

WHERE timediff is BETWEEN (MINUTE_SECOND,'timestamp' )

也不是太有意义.我知道您要如何处理此问题,但似乎与互联网上的示例结合在一起了.您想要的可能是这样的：

SELECT *

FROM tag_logs

WHERE timestamp BETWEEN timestamp1 AND timestamp2.

但是,您似乎想为每个记录计算时差.

您想要的是可以逐行处理并计算两条记录之间的时间戳差异的东西：

SELECT COUNT(*)

FROM (SELECT p1.id,p1.time,TIMEDIFF(p2.time,p1.time)

AS timedifference

FROM tag_logs p1

INNER JOIN tag_logs p2

ON p2.id = (p1.id + 1)

ORDER BY p1.id ASC) time_table

WHERE time_table.timedifference >= '00:05:00'

尝试#2！

SELECT COUNT(*)

FROM (SELECT p1.id,p1.ts,TIMEDIFF(timestamp(p2.ts),timestamp(p1.ts)) AS timedifference

FROM tag_logs p1

INNER JOIN tag_logs p2

ON p2.id = (p1.id + 1)

ORDER BY p1.id ASC) time_table

WHERE TIME_TO_SEC(time_table.timedifference) >= TIME_TO_SEC('00:10:00')

37次迭代….

SELECT COUNT(*)

FROM (SELECT p1.id,UNIX_TIMESTAMP(p2.ts) - UNIX_TIMESTAMP(p1.ts) AS timedifference

FROM tag_logs p1

INNER JOIN tag_logs p2

ON p2.id = (p1.id + 1)

ORDER BY p1.id ASC) time_table

WHERE time_table.timedifference >= TIME_TO_SEC('1:00:00')