时间戳

首页 > 解决方案 > 对于特定的日期时间，查找前后日期时间最近的记录

mysql - 对于特定的日期时间，查找前后日期时间最近的记录

问题描述

让我们有一个包含 DateTime 列的 MySQL 数据库表when。这样的列可以携带除 NULL 之外的任何 DateTime 值。

如何创建一个包含 、 和 列的表when，whenA其中whenB所有全分钟 DateTime 值 (YYYY-MM-DD HH:MM:00) 在最小值和最大值之间，when并且whenA是最接近的 DateTime 或之前，并且whenB是值上或之后最接近的日期时间when？如果存在确切的when值，whenA并且whenB将与 相同when。如果不存在 on 或 before 或 on 或 after 的记录，则 NULL 将分别填入whenAor whenB。

显然，有很多可能的方法来制作它，但问题是什么应该是最有效的？

标签： mysqlsqldatabasedatedatetime

解决方案

您可以使用递归 CTE 构造值。然后你可以使用相关的子查询来得到你想要的：

with recursive cte as (
  select from_unixtime(floor(unix_timestamp(min(whent)) / 60) * 60) as minw,
         max(whent) as maxw
      from t
      union all
      select minw + interval 1 minute, maxw
      from cte
      where minw < maxw
     )
select minw,
       (select max(t2.when)
        from t t2
        where t2.when = cte.minw
       ) as when,
       (select max(t2.when)
        from t t2
        where t2.when <= cte.minw
       ) as when_before,
       (select min(t2.when)
        from t t2
        where t2.when >= cte.minw
       ) as when_after
from cte;

请注意，这when是一个非常糟糕的列名称，因为它是 SQL 关键字和 MySQL 中的保留字。

这是一个 db<>fiddle。

我应该注意，如果您有某种数字或计数表，也可以使用它。

推荐阅读