Assuming your database ONLY contains the log entries this should work....and be
pretty fast too since rowid is already indexed and there areYou no other
lookups.
You can add your own rowid to make this work otherwise. Just do a
max(myrowid)+1 on your insert.
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE log(d date);
INSERT INTO "log" VALUES('2011-04-21 00:00');
INSERT INTO "log" VALUES('2011-04-21 00:01');
INSERT INTO "log" VALUES('2011-04-21 00:02');
INSERT INTO "log" VALUES('2011-04-21 00:03');
INSERT INTO "log" VALUES('2011-04-21 00:04');
INSERT INTO "log" VALUES('2011-04-21 00:06');
INSERT INTO "log" VALUES('2011-04-21 00:07');
INSERT INTO "log" VALUES('2011-04-21 00:08');
INSERT INTO "log" VALUES('2011-04-21 00:10');
INSERT INTO "log" VALUES('2011-04-21 00:13');
COMMIT;
select l2.d,l1.d,(strftime('%s',l1.d)-strftime('%s',l2.d)-60)/60 from log
l1,log l2 where (l1.rowid=l2.rowid+1) and (strftime('%s',l1.d) -
strftime('%s',l2.d) != 60);
2011-04-21 00:04|2011-04-21 00:06|1
2011-04-21 00:08|2011-04-21 00:10|1
2011-04-21 00:10|2011-04-21 00:13|2
The 3rd column can have a sum() to find out how many periods have been missed.
Easy to modify this into a daily/weekly/monthly report..
Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate
________________________________
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of Andrew Lindsay [andrew.lind...@westnet.com.au]
Sent: Wednesday, April 20, 2011 6:34 PM
To: sqlite-users@sqlite.org
Subject: EXT :[sqlite] Request for help with SQLite Query to return missing
Date/Time Ranges
Dear Group,
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.
Any assistance would be greatly appreciated.
Regards
Andrew Lindsay
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
