mysql - 计算出某个值在 mysql 数据库中存在了多长时间

Question

是否有一个查询可以用来计算第一次看到某个值时与运行查询时之间的时间差，其中值之间的时间差不超过半小时？例如，如果有如下数据

--------------------------------------------------
| ID| DATE             | TIME                 |
--------------------------------------------------
| 1 | 18/07/2018       | 10:33:15              |
--------------------------------------------------
| 1 | 18/07/2018       | 10:53:15              |
--------------------------------------------------
| 1 | 18/07/2018       | 11:03:15              |
--------------------------------------------------
| 1 | 18/07/2018       | 11:53:15              |
--------------------------------------------------

结果是

--------------------------------------------------
| ID|    DATE             | TimePresent           |
--------------------------------------------------
| 1 |    18/07/2018       | 10:33:15              |
-------------------------------------------------- 

因此计算出 10:33 到 11:10 之间的时间，因为输入的数据相隔不超过 30 分钟，并排除 11:53，因为它距离上次输入的 11:03 超过 30 分钟我希望我说得有道理

我之后的结果是这样的。

--------------------------------------------
| ID| DATE             | TimePresent       |
--------------------------------------------
| 1 | 18/07/2018       | 00.45             |
--------------------------------------------
| 2 | 18/07/2018       | 24:00             |
--------------------------------------------
| 3 | 18/07/2018       | 45:00             |
--------------------------------------------
| 4 | 18/07/2018       | 72:15             |
--------------------------------------------------

例如

SELECT ID,
       Avg(id 1st seen, now) AS timepresent
FROM database.table
WHERE TIME BETWEEN value inputs < now() - interval 30 MINUTE

如果没有办法确定值输入之间的时间，那么是否有办法如下执行？

SELECT ID,
       Avg(id 1st seen, now) AS timepresent
FROM database.table
WHERE date = CURDATE()
GROUP BY ID

Answer 1

请参阅下面的链接

http://sqlfiddle.com/#!9/486850/81

CREATE TABLE Test (

id int primary key,
Present varchar(10),
Date date,
Time time
);

INSERT INTO Test (id, Present, Date, Time)
Values (1, 'Present', '2018-07-18', '10:13:55' ),
(2, 'Present', '2018-07-18', '10:10:55' );

查询分钟数:

SELECT 
id,
Present,
Date,
Time,
Current_Time as CurrentTime,
TIMESTAMPDIFF(MINUTE, time, CURRENT_TIMESTAMP()) AS DifferenceMins
FROM Test

查询 hh:mm:ss

SELECT 
id,
Present,
Date,
Time,
Current_Time as CurrentTime,
SEC_TO_TIME(TIMESTAMPDIFF(SECOND, CURRENT_TIMESTAMP(),time)) AS 
TimeDifference
FROM Test

结果:

| id | Present |       Date |     Time | CurrentTime | DifferenceMins |
|----|---------|------------|----------|-------------|----------------|
|  1 | Present | 2018-07-18 | 10:13:55 |    13:33:28 |            199 |
|  2 | Present | 2018-07-18 | 10:10:55 |    13:33:28 |            202 |

我认为这就是您正在寻找的粗略逻辑。然后，您可以添加过滤 30 分钟等的位置。时间是添加数据的时间，当前时间是执行查询的确切时间。