时间戳

首页 > 解决方案 > 获取两个日期之间的时间

sql - 获取两个日期之间的时间

问题描述

我试图在两个不同的行中获取两个日期之间的时间。

数据集：

用户 ID 时间 地位
用户3 4 2021-02-01 14:00:00 出去
用户1 2 2021-02-01 12:00:00 出去
用户2 1 2021-02-01 10:00:00 出去
用户1 2 2021-02-01 09:00:00 在
用户2 1 2021-02-01 08:00:00 在
用户3 4 2021-02-01 08:30:00 在

我想获得什么：

用户 ID 时间
用户3 4 07:30:00
用户1 2 03:00:00
用户2 1 02:00:00

由于我不知道用户何时会打开（IN）或关闭（OUT）作业，所以这些行可能都混在一起了。

到目前为止，我尝试进行第一个查询：

SELECT * 
    FROM table 
    WHERE DATE(date) BETWEEN '2021-02-01' AND '2021-02-02' 
    ORDER BY date DESC;

然后我在考虑循环结果数组，并从那里进行数学运算，但我想知道是否有任何方法可以仅在 SQL 中执行此操作。

标签： sqlmariadb

解决方案

您可以先将结果排序，然后使用 Lead() 从下一行获取上一个时间。虽然我不是 mariaDB 方面的专家，但下面的查询应该可以工作：

  with userresult as(
    SELECT *,lead(TIME,1)OVER(PARTITION BY id ORDER BY STATUS DESC) previoustime,ROW_NUMBER()over(partition by id order by status desc) rownum FROM USERS
where  DATE(date) BETWEEN '2021-02-01' AND '2021-02-02' 
    )
    select user,id,timediff(time,previoustime)time from userresult where rownum=1

如果您的 mariaDB 版本低于 10.2，您可以尝试如下简单的 sql：

select username,id,timediff(time,(select max(time) from users u where status='in'and u.id=us.id ))
from users us where status='out'

推荐阅读