Andrew Lindsay <andrew.lindsay@...> writes:
[...]
> 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.
>
[...]
very easy:
1. calculation
60*24 = 1,440 minutes ( 1 day)
1,440 * 31 = 44,640 minutes ( 1 month, only an approximation
because not every month has 31 days)
44,640 * 15 = 669,600 minutes (15 months)
-> produce ~669,600 entries that represent your minutes of a day
assuming you have a timestamp like YYYY-MM-DD HH:MM:SS in your database
your 669,600 look like this:
2011-04-21 00:00
2011-04-21 00:01
...
you can produce this very simply with a spreadsheet program like
OpenOffice Calc (the latest version has more than 1,000,000 rows)
2. Import the whole thing in a sqlite table
CREATE Table check(
minute TEXT,
PRIMARY KEY(minute)
);
3. EXCEPT
SELECT minute
FROM check
EXCEPT
SELECT substr(timestamp,1,16)
FROM
yourtable
;
That's it - I assume here that you want to check every minute but it's
easy to adapt this
greetings
oliver
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
