On Wed, 28 Aug 2013 14:58:35 -0700 (PDT) jdp12383 <[email protected]> wrote:
> Each record is a recording up to 3 min. I am trying to retrieve one > record per continuous recording. ... > CREATE TABLE recordings ( > [key] INTEGER PRIMARY KEY ASC AUTOINCREMENT, > filename VARCHAR(50), > start_datetime DATETIME, > end_datetime DATETIME, > deleted BOOLEAN > ); This is a simplified version of finding overlapping time series, which I discuss at http://www.schemamania.org/sql/#overlapping.dates. To write your query I changed your table: CREATE TABLE recordings ( filename VARCHAR(50) unique, starts DATETIME, ends DATETIME, primary key (starts) ); because 1. Both start time and filename are unique 2. start time is the key to the time sequence 3. "_datetime" is wordy; don't name things after their type 4. Deleted rows belong in another table that will probably grow to carry when it was deleted, by whom, and why. Restricting recordings to the active set will minimize the work to find contiguous series. I also recommend a constraint enforcing "starts <= ends". I changed the data (see below) because *meaning* of recordings.ends should be an "exclusive end", what's knows as a half-open interval. That makes the question of "does b follow a" one of equality. It is likely not the case that exactly one second transpires between two files. Rather, there is *no* time between them, implying that the end time is at the "end of that second". Much easier to think of both as being the beginning of the second. With that behind us, here's your self-join: create view vrecordings as select a.starts, max(b.ends) as ends from recordings a join recordings b on a.ends <= b.starts -- includes same row where not exists ( -- no row between a and b starts a new sequence select 1 from recordings as s where starts between a.ends and b.starts and not exists ( -- i.e. not a starter select 1 from recordings where ends = s.starts ) ) and not exists ( -- "a" is a starter select 1 from recordings where ends = a.starts ) and not exists ( -- "b" is an ender select 1 from recordings where starts = b.ends ) group by a.starts ; > If there is gap more than 3 min then it would be considered as a > separate recording. If that's the true requirement, you'll have to replace the equality tests with datetime arithmetic. For simplicity, I'd keep the query above, but instead of reading recordings directly I'd have it read a view, say "vtrecordings", that replaces "ends" with the value of the "starts" column of the succeeding row, if extant, else the row's own "ends" value. Do your date magic in that view, and use equality to find the sequences. For your convenience, the insert statements follow. --jkl INSERT INTO recordings (filename, starts, ends) VALUES ('f1', '2013-08-26 00:00:00', '2013-08-26 00:03:00'); INSERT INTO recordings (filename, starts, ends) VALUES ('f2', '2013-08-26 00:03:00', '2013-08-26 00:06:00'); INSERT INTO recordings (filename, starts, ends) VALUES ('f3', '2013-08-26 00:06:00', '2013-08-26 00:09:00'); INSERT INTO recordings (filename, starts, ends) VALUES ('f4', '2013-08-26 00:14:00', '2013-08-26 00:17:00'); INSERT INTO recordings (filename, starts, ends) VALUES ('f5', '2013-08-26 00:17:00', '2013-08-26 00:20:00'); INSERT INTO recordings (filename, starts, ends) VALUES ('f6', '2013-08-26 00:20:00', '2013-08-26 00:23:00'); INSERT INTO recordings (filename, starts, ends) VALUES ('f7', '2013-08-26 00:30:00', '2013-08-26 00:33:00'); INSERT INTO recordings (filename, starts, ends) VALUES ('f8', '2013-08-26 00:33:00', '2013-08-26 00:36:00'); INSERT INTO recordings (filename, starts, ends) VALUES ('f9', '2013-08-26 00:36:00', '2013-08-26 00:39:00'); INSERT INTO recordings (filename, starts, ends) VALUES ('f10', '2013-08-26 00:44:00', '2013-08-26 00:47:00'); INSERT INTO recordings (filename, starts, ends) VALUES ('f11', '2013-08-26 00:47:00', '2013-08-26 00:50:00'); INSERT INTO recordings (filename, starts, ends) VALUES ('f12', '2013-08-26 00:50:00', '2013-08-26 00:53:00'); _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
