Andrew Lindsay <[email protected]> wrote:
> I am trying to search an SQL database that is meant to have entries logged
> every minute for a period of approximately 15 months.
>
> I want to create a query that will search through the database and tell me
> for which periods I do not have any entries.
Something like this perhaps:
select * from (
select *, julianday(t1.timestamp) - julianday(
(select t2.timestamp from MyTable t2
where t2.timestamp < t1.timestamp order by t2.timestamp desc limit 1)
) as Gap
from MyTable t1
)
where Gap > 1.0/1440.0 order by Gap desc;
(1.0/1440.0) is one minute (there are 1440 minutes in a day).
--
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
