MySQL -查找连续日期时间之间的最大时间差

e3bfsja2 于 2021-06-23 发布在 Mysql

关注(0)|答案(4)|浏览(143)

我有一个包含日期时间列表的表。
我想找到连续的datetime之间的最长时间，即找到任何两个datetime条目之间的最大距离，它们在时间轴上彼此相邻。把它想象成一个“最长的条纹”-从一个重置到下一个重置的最长时间。
例如：

mysql> select * from resets order by datetime asc;
+----+---------------------+-------------+---------------------+---------------------+
| id | datetime            | activity_id | created_at          | updated_at          |
+----+---------------------+-------------+---------------------+---------------------+
|  7 | 2014-12-30 20:38:22 |           1 | 2015-01-06 20:38:22 | 2015-01-06 20:38:22 |
|  3 | 2014-12-31 20:38:22 |           1 | 2015-01-06 20:38:22 | 2015-01-06 20:38:22 |
|  5 | 2015-01-01 20:38:22 |           1 | 2015-01-06 20:38:22 | 2015-01-06 20:38:22 |
|  4 | 2015-01-02 20:38:22 |           1 | 2015-01-06 20:38:22 | 2015-01-06 20:38:22 |
|  6 | 2015-01-03 20:38:22 |           1 | 2015-01-06 20:38:22 | 2015-01-06 20:38:22 |
|  1 | 2015-01-04 20:38:22 |           1 | 2015-01-06 20:38:22 | 2015-01-06 20:38:22 |
|  2 | 2015-01-05 20:38:22 |           1 | 2015-01-06 20:38:22 | 2015-01-06 20:38:22 |

 etc...

字符串
从上面的示例数据集中，我想知道以下哪个时间差更大：

在第7行和第3行之间
在第3行和第5行之间
在第5行和第4行之间
等等

（显然，它们之间的间隔都是24小时，我在寻找一个通用的解决方案。）
这很容易用普通的编程语言完成，方法是迭代有序数组，存储连续值之间的差，并选择最大的一个。
有没有一个简单的方法来做这件事，只使用**SQL？

更新：

对我有用的查询是

SELECT MAX(DATEDIFF(r.next_datetime, r.datetime))
    FROM (

    # finds each datetime and the next consecutive one in the table
    SELECT r1.datetime as datetime, (

        # finds the next consecutive datetime
        SELECT datetime
        FROM resets r2
        WHERE r2.datetime > r1.datetime
        ORDER BY datetime ASC
        LIMIT 1
    ) as next_datetime

    FROM resets as r1
    ORDER BY r1.datetime

) as r;

型
最里面的查询SELECT datetime FROM resets r2...负责查找列表中下一个大于当前日期的日期时间。请注意，这个查询是有序的，限制为1。这对我来说是最困难的部分。
剩下的很简单。对于表中的每一行，我们选择datetime值和表中下一个连续的datetime。最外面的查询找到我们刚刚创建的任何datetime对之间的最大差异-“连胜”。
我选择了@OllieJones给出的答案，因为它是最简洁和最好的解释，即使我更喜欢“纯SQL”解决方案。

mysql

来源：https://stackoverflow.com/questions/27832148/mysql-find-the-largest-time-difference-between-consecutive-datetimes

4条答案

按热度按时间

luaexgnf1#

此查询将计算连续行之间的时间差，并显示最大的时间差--连胜的时间长度。如果需要整行，则需要Gordon的查询。

SELECT MAX(diff)
  FROM (
          SELECT TIMEDIFF(datetime,@prev) AS diff,
                 (@prev:=datetime) AS datetime  
            FROM resets, 
                 (SELECT @prev:=(SELECT MIN(datetime) FROM resets)) AS init
           ORDER BY datetime
       ) AS diffs

字符串
这是如何工作的？
首先，它是单行查询和表之间的交叉联接。单行查询如下所示：

(SELECT @prev:=(SELECT MIN(datetime) FROM resets))

型
它将用户定义的值@prev设置为表中最低/最早的datetime。这是MySQL在查询开始时初始化用户定义变量的一个技巧。
然后，SELECT子句中会有两个数据行：

SELECT TIMEDIFF(datetime,@prev) AS diff,
                 (@prev:=datetime) AS datetime

型
第一个函数取当前行的datetime与@prev的值之间的时间差，第二个函数将@prev的值更新为当前行的datetime。
因此，内部查询会输出一个时间戳列表，以及与ORDER BY datetime中前一个时间戳的差异。
外部查询SELECT MAX(diff)从内部查询中获取diff的最大值--最长的连胜。
让我们明确一点：这是MySQL特有的恶作剧。纯SQL应该是声明性的，而不是过程性的。但是，使用用户定义的@prev变量的这个技巧让我们以一种有用的方式混合了声明性和过程性代码，即使它有点晦涩。

赞(0）回复(0）举报 2023-11-16

f87krz0w2#

您可以使用相关子查询计算下一个日期时间，然后通过排序找到最大的日期时间：

select r.*
from (select r.*,
             (select datetime
              from resets r2
              where r2.datetime > r.datetime
              order by datetime
              limit 1
             ) as next_datetime
      from resets r
     ) r
order by timestampdiff(second, datetime, next_datetime) desc
limit 1;

字符串

赞(0）回复(0）举报 2023-11-16

bfnvny8b3#

不幸的是，MySQL不支持lag来获取前一行的日期时间，但是你可以使用变量来模拟它。

select max(timediff(datetime,prevDate)) from (
    select *, @prevDate prevDate, @prevDate := datetime
    from resets
    order by datetime 
) t1

字符串
或者如果要选择整行

select * from (
    select *, @prevDate prevDate, @prevDate := datetime
    from resets
    order by datetime 
) t1 order by timediff(datetime,prevDate) desc limit 1

型

赞(0）回复(0）举报 2023-11-16

xa9qqrwz4#

使用window functions，您可以获得所有时间差的列表，并仅限于一个结果：

SELECT
datetime,
LEAD(datetime) OVER (ORDER BY datetime) AS next,
TIMESTAMPDIFF(SECOND, datetime, LEAD(datetime) OVER (ORDER BY datetime)) AS diff
FROM resets ORDER BY diff DESC LIMIT 1;

字符串

赞(0）回复(0）举报 2023-11-16

我来回答

MySQL -查找连续日期时间之间的最大时间差

4条答案

相关问题

热门标签

最新问答