jdp12383 wrote:
> This table stores webcam recordings. Each record is a recording up to 3 min.
> I am trying to retrieve one record per continuous recording. If there is
> gap more than 3 min then it would be considered as a separate recording.
>
> CREATE TABLE recordings (
> [key] INTEGER PRIMARY KEY ASC AUTOINCREMENT,
> filename VARCHAR(50),
> start_datetime DATETIME,
> end_datetime DATETIME,
> deleted BOOLEAN
> );
>
> The result should be,
>
> recording1 2013-08-26 00:00:00 2013-08-26 00:09:00
> recording2 2013-08-26 00:14:00 2013-08-26 00:23:00
> recording3 2013-08-26 00:30:00 2013-08-26 00:39:00
> recording4 2013-08-26 00:44:00 2013-08-26 00:53:00
Finding all recordings that make up a continuous recording by joining
the table with itself is not possible in SQLite because there could be
arbitrarily many recordings in one group.
However, it is possible to find groups by inverting the check: a record
is the first one in a group (r1) if the difference to the previous
recording (r2) is more than three minutes (or if there is no previous
recording). Once we have the start of a group, we can compute the end by
finding the first following record (r3) whose difference to the next
record (r4) is larger than three minutes (or which has no next record):
SELECT r1.start_datetime AS start,
(SELECT MIN(r3.end_datetime)
FROM recordings AS r3
WHERE r3.start_datetime >= r1.end_datetime
AND IFNULL((SELECT MIN(strftime('%s', r4.start_datetime))
FROM recordings AS r4
WHERE r4.start_datetime >= r3.end_datetime),
99999999999) - strftime('%s', r3.end_datetime) > 3*60
) AS end
FROM recordings AS r1
WHERE strftime('%s', r1.start_datetime) -
IFNULL((SELECT MAX(strftime('%s', r2.end_datetime))
FROM recordings AS r2
WHERE r2.end_datetime <= r1.start_datetime),
0) > 3*60
(This computes the gap as the difference between the end and the next
start, which is just one second in your examples. You might want to
reduce the 3*60 threshold.)
(everything plagiarized from <http://stackoverflow.com/questions/18518454>)
Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
