在SQLite中检测日期之间的间隙-解网

问：

我有一个具有以下结构的SQLite表：

如您所见，该列每 1 分钟有一个新行，但有时该过程会停止，并且存在“时间间隔”，如带下划线的示例所示。我需要通过查询找到这些差距，因为这个表很大。sampleDateSQL

可能的预期结果输出可能是：

2023-03-20 12:56:27
2023-03-29 10:46:46

这可能表示这两个日期之间存在差距。

sql sqlite 间隙和孤岛

with cte as (
  select *, 
          lead(sampleDate, 1, sampleDate) over (order by sampleDate) as nextDate,
          CAST(strftime('%M', lead(sampleDate, 1, sampleDate) over (order by sampleDate)) as integer) 
          - CAST(strftime('%M', sampleDate) as integer) as diff
  from mytable
)
select sampleDate, nextDate
from cte
where diff > 1

0赞 Nandalal Seth 10/17/2023 #2

或者您可以使用左反联接来获取没有下一分钟记录的行吗？下面提供的示例 -

with cte as (
  select 4556 id, datetime('2023-03-20 12:54:27') sampleDate
  union ALL
  select 4557 id, datetime('2023-03-20 12:55:27') sampleDate
  union ALL
  select 4558 id, datetime('2023-03-20 12:56:27') sampleDate
  union ALL
  select 4559 id, datetime('2023-03-29 10:46:46') sampleDate
  union ALL
  select 4560 id, datetime('2023-10-06 10:52:47') sampleDate
  union ALL
  select 4561 id, datetime('2023-10-06 10:53:47') sampleDate
 )
 select a.* from cte a 
 left join cte b ON  b.sampledate = datetime(a.sampleDate,'+1 minutes') -- if wanted the gap to be exact one minutes
            --  ON b.sampledate between datetime(a.sampleDate,'+1 seconds')  and datetime(a.sampleDate,'+1 minutes')  -- if you wanted the gap to be anywhere under a minute
 where b.id is NULL

上一个：在子窗口 SQL Presto 中执行具有特定条件的值计数

下一个：我想找到每次子猫更改或返回时的第一个日期，特定子猫可能会被报告不止一次 [重复]

在SQLite中检测日期之间的间隙

Detect gaps between dates in SQLite

评论