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

8zzbczxx  于 2023-10-23  发布在  SQLite
关注(0)|答案(2)|浏览(156)

我有一个SQLite表,结构如下:

正如您所看到的,sampleDate列每隔1分钟就有一个新行,但有时该过程会停止,并且存在“时间间隙”,如下划线示例所示。我需要用SQL查询来找到这些差距,因为这个表很大。
可能的预期结果输出可能是:

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

这可能意味着这两个日期之间存在差距。

zsohkypk

zsohkypk1#

您可以使用LEAD()函数来检索下一个日期,然后计算差值来检索间隙:

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
n3ipq98p

n3ipq98p2#

或者你可以使用一个左反连接来获取没有下一分钟记录的行吗?示例如下-

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

相关问题