mysql-是否有一条语句来计较数据库中读数之间的时刻差？

我正在实行行使2个读取器举办RFID会见节制,这相同于两身分身份验证体系.
我一向在实行从数据库中的差异RFID阅读器计较两个时刻戳之间的时刻差.我实行了很多差异的要领来查询功效,但始终获得零或错误.

我实行了几种查询呼吁以各类组合计较时差ex：timestampdiff.我已经展示了一些我实行过的.

   SELECT COUNT(*) FROM tag_logs
   WHERE tag_logs = tag_no
   BETWEEN timestamp ('timestamp','timestamp');

SELECT *
FROM tag_logs
WHERE timediff is BETWEEN (MINUTE_SECOND,'timestamp','timestamp' )

我祈望输出计较行数并搜查它是否便是或大于特定命. 最佳谜底 您可以行使MySQL TIMEDIFF function：

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'

Here is a functioning SQL fiddle for example

实行#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')

And another SQL Fiddle!

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')

（编辑：湖南网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!