SQLite compare dates between two consecutive rows

Question

I have a table of media files:

id title date

The date is in epoch format, and for example, I have 3 photos:

1  xxxxx 1644777107349(6:31:47.349)
2  wwwww 1644777110138(6:31:50.138)
3  zzzzz 1644777117453(6:31:57.453)

I want to get the count of photos that were taken with a gap of 5 to 30 seconds, for example: 2 and 3 were taken with a gap of 7 seconds so I want to get 2. I want to compare every row with the consecutive row.

This is the SQL query:

SELECT Count(*)
FROM   (SELECT A._id
    FROM   media_files A
        INNER JOIN media_files B
            ON( Abs(A.added_time - B.added_time) >= 5000 )
                 AND A._id != B._id
                 AND A.type = 'image'
                 AND B.type = 'image'
    WHERE  Abs(A.added_time - B.added_time) <= 30000
    GROUP  BY A._id) 

And for the above example, I always get 3 instead of 2, any idea what is the problem?

Answer 1

Here is a test setup and a query which returns only the images taken between 5 and 30 seconds after the previous one.NB I'm working on mySQL so where I have put 5 and 30 seconds it may be that in SQLlite you need to put 5000 and 30000.

CREATE TABLE media_files (
  id INT,
  title VARCHAR(25),
  added_date DATETIME,
  type VARCHAR(25)
  );
INSERT INTO media_files VALUES ( 1,  'xxxxx', '2022-02-13 6:30:47.349','image');
INSERT INTO media_files VALUES ( 1,  'xxxxx', '2022-02-13 6:31:27.349','image');
INSERT INTO media_files VALUES ( 1,  'xxxxx', '2022-02-13 6:31:47.349','image');
INSERT INTO media_files VALUES ( 2,  'wwwww', '2022-02-13 6:31:50.138','image');
INSERT INTO media_files VALUES ( 3,  'zzzzz', '2022-02-13 6:31:57.453','image');

SELECT    id,
          added_date ad,
          added_date -
        ( SELECT MAX(added_date) 
          FROM media_files m
           WHERE m.added_date < mf.added_date ) DIF
FROM  media_files mf
WHERE (          added_date -
        ( SELECT MAX(added_date) 
          FROM media_files m
          WHERE m.added_date < mf.added_date )) > 5
AND   (          added_date -
        ( SELECT MAX(added_date) 
          FROM media_files m
          WHERE m.added_date < mf.added_date )) < 30;

id | ad | DIF -: | :------------------ | --: 1 | 2022-02-13 06:31:47 | 20 3 | 2022-02-13 06:31:57 | 7