Not common filtering in MySQL

Question

Have some row set with next data

s1  in1  out1  t1  date1
s2  in2  out2  t2  date2
...
si  ini  outi  ti  datei

The table structure is:

s1    BIGINT PK 
in
out
t     BIGINT 
date  DATETIME

where date - date with time when session start, and time - how long will the session (in seconds). I need filtering in query with this condition:

row write to the result set if:

    datei+1-datei<ti

It's because DB table have some amount corrupted rows. I can make this programmatically. But is it possible to make this in MySQL query? Most interesting are row offset.

UPDATE When I say datei+1 - it's mean date from next row (offset by index)

Can you show the table structure? I mean what is the column types? — Arion, Jun 13 '14 at 08:35
When you say datei+1. Do you want to increase `YEAR`, `MONTH`, `DAY`, `MINUTES`, `SECONDS` or `MILISECONDS`? — Arion, Jun 13 '14 at 09:21

score 0 · Accepted Answer · edited May 23 '17 at 11:57

If I get you right. You might what something like this:

SELECT
    *
FROM
    (
        SELECT
        Table1.s1,
        Table1.date,
        Table1.t,
        (
        SELECT
           MIN(tbl.s1) AS s1
        FROM
           Table1 AS tbl
        WHERE 
            tbl.s1 > Table1.s1
        ) as NextId
    FROM
        Table1
) AS tblInner
JOIN Table1 AS Next
    ON tblInner.NextId=Next.s1
WHERE TIMESTAMPDIFF(SECOND,Next.date,tblInner.date)<tblInner.t

References:

Not common filtering in MySQL

1 Answers1