Actually, with this criteria, you can -- in a roundabout way. select the timestamps by a large period, perhaps daily or monthly. aggregate on that rough timestamp, and also select the count. Anywhere the count is lower than expected, you are missing data.
You can then repeat over relevant ranges with finer aggregation to identify exactly what is missing. A completely missing block might not be noticed, but unless the stop and start exactly matched the missing data, you would see the border anomalies. A having clause would be useful for selecting just the anomalous aggregations (provided they are uniform). --David Garfield Simon Slavin writes: > > On 21 Apr 2011, at 12:34am, Andrew Lindsay 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. > > You can't find missing data. Your fastest way to do it would just be to > write code retrieve all the datastamps, turn them into period numbers, then > look down the list for missing ones. A slower way would be to use a SELECT > which retrieved all entries, with a sub-SELECT which looked for all entries > in the following period. > > Simon. > _______________________________________________ > 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
