On Thu, Apr 21, 2011 at 12:57 AM, Oliver Peters <oliver....@web.de> wrote: > > 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 > ... >
[..] > > That's it - I assume here that you want to check every minute but it's > easy to adapt this With possible 'adjustments' for switching to daylight savings time and back, depending on what timezone the original data is stored in. If it was UTC, then no problem. But if it was in you local timezone, that used daylight savings time, then daylight savings time changes will cause a 1 hour gap, and a 1 hour overlap in the data once a year [each]. > greetings > oliver -- Phil _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
