Hi,
I am new to SQLite. I am using SQLite Manager Firefox add-on. I have created
a database and a table. 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. Below is the script.
[CODE]
CREATE TABLE recordings (
[key] INTEGER PRIMARY KEY ASC AUTOINCREMENT,
filename VARCHAR(50),
start_datetime DATETIME,
end_datetime DATETIME,
deleted BOOLEAN
);
INSERT INTO [recordings] ([filename], [start_datetime], [end_datetime],
[deleted]) VALUES ('f1', '2013-08-26 00:00:00', '2013-08-26 00:03:00', 0);
INSERT INTO [recordings] ([filename], [start_datetime], [end_datetime],
[deleted]) VALUES ('f2', '2013-08-26 00:03:01', '2013-08-26 00:06:00', 0);
INSERT INTO [recordings] ([filename], [start_datetime], [end_datetime],
[deleted]) VALUES ('f3', '2013-08-26 00:06:01', '2013-08-26 00:09:00', 0);
INSERT INTO [recordings] ([filename], [start_datetime], [end_datetime],
[deleted]) VALUES ('f4', '2013-08-26 00:14:00', '2013-08-26 00:17:00', 0);
INSERT INTO [recordings] ([filename], [start_datetime], [end_datetime],
[deleted]) VALUES ('f5', '2013-08-26 00:17:01', '2013-08-26 00:20:00', 0);
INSERT INTO [recordings] ([filename], [start_datetime], [end_datetime],
[deleted]) VALUES ('f6', '2013-08-26 00:20:01', '2013-08-26 00:23:00', 0);
INSERT INTO [recordings] ([filename], [start_datetime], [end_datetime],
[deleted]) VALUES ('f7', '2013-08-26 00:30:00', '2013-08-26 00:33:00', 0);
INSERT INTO [recordings] ([filename], [start_datetime], [end_datetime],
[deleted]) VALUES ('f8', '2013-08-26 00:33:01', '2013-08-26 00:36:00', 0);
INSERT INTO [recordings] ([filename], [start_datetime], [end_datetime],
[deleted]) VALUES ('f9', '2013-08-26 00:36:01', '2013-08-26 00:39:00', 0);
INSERT INTO [recordings] ([filename], [start_datetime], [end_datetime],
[deleted]) VALUES ('f10', '2013-08-26 00:44:00', '2013-08-26 00:47:00', 0);
INSERT INTO [recordings] ([filename], [start_datetime], [end_datetime],
[deleted]) VALUES ('f11', '2013-08-26 00:47:01', '2013-08-26 00:50:00', 0);
INSERT INTO [recordings] ([filename], [start_datetime], [end_datetime],
[deleted]) VALUES ('f12', '2013-08-26 00:50:01', '2013-08-26 00:53:00', 0);
[/CODE]
The result should be,
[CODE]
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
[/CODE]
i would appreciate any help in resolving this.
Thanks,
Jdp
--
View this message in context:
http://sqlite.1065341.n5.nabble.com/T-SQL-to-retrieve-needed-records-tp70760.html
Sent from the SQLite mailing list archive at Nabble.com.
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
