• Welcome to SQLitening Support Forum.
 

News:

Welcome to the SQLitening support forums!

Main Menu

T-SQL to retrieve needed records

Started by jdp12383, August 28, 2013, 06:57:51 PM

Previous topic - Next topic

jdp12383

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.

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);


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


I would appreciate any help in resolving this.

Thanks,
Jdp

cj

Do you have any PowerBASIC code to look at?
Where is the blob of the recording stored?

#COMPILE EXE
#DIM ALL
#INCLUDE "sqlitening.inc"
FUNCTION PBMAIN () AS LONG
  LOCAL s AS STRING
  LOCAL x,columns AS LONG
  slOpen "recordings.db3","C"
  slexe "drop table if exists recordings"
  slexe "create table if not exists recordings " +_
      "(key integer primary key autoincrement," + _
      "filename,start_datetime,end_datetime,deleted)"
  s = "Begin Immediate;"
  s=s+"Insert into recordings values(null,'Recording1', '2013-08-26 00:00:00', '2013-08-26 00:03:00', 0);"
  s=s+"Insert into recordings values(null,'Recording2', '2013-08-26 00:03:01', '2013-08-26 00:06:00', 0);"
  s=s+"End"
  slExe s
  slSel "select * from recordings"
  columns = slGetColumnCount
  RESET s
  DO WHILE slgetrow
    FOR x = 1 TO columns
      s = s + slf(x) + " "
    NEXT
    s = s + $CR
  LOOP
  ? s,,"Results"
END FUNCTION
"No email alerts being received"  Please often check back.

jdp12383

Hi cj,

No its not in PowerBASIC.

Sorry, I don't understand what your code is doing. I am trying to write T-SQL which retrieve me the result as I have posted. The binary data column I have not posted for simplicity.

Do you have any idea how can I achieve this?

Thanks,
Jdp

cj

Sorry.
I only know how to call SQLitening using PowerBASIC or Visual Basic 6.
"No email alerts being received"  Please often check back.

Bern Ertl

If it were me, I'd add another field ( 'NewRec DEFAULT 0' ) to the table.  Then I'd create a trigger to compare the start time of any new record to the latest end time of any existing record.  If the gap is greater than or equal to 3 min, NewRec = RowID.

Then it would be easy to retrive all records where NewRec > 0. That should be the first record of every "recording" if I understood the OP correctly.