php - Mysql: Gap detection query not detecting gaps -

it seems though gap detection picking whatever set timedifference , doing every interval based on that.

so here's explanation of data structure , i'm after:

i have database that's set this:

(schema name) historical      -cid      int uq ai nn       -id       int pk      -location varchar(255)      -status   varchar(255)      -time     datetime 

my data comes in looking (example 5 rows selected id)

 433275 | 97  | mylocation | ok | 2013-08-20 13:05:54  433275 | 97  | mylocation | ok | 2013-08-20 13:00:54  433275 | 97  | mylocation | ok | 2013-08-20 12:25:54  433275 | 97  | mylocation | ok | 2013-08-20 12:20:54  433275 | 97  | mylocation | ok | 2013-08-20 12:15:54 

in case above you'll notice i'm missing data 12:25:54 -> 13:00 id 97. trying write report tell me the: start of downtime, duration of downtime, , end of downtime (which i've been handling in php adding timediff time)

here's code (php -> mysql) (non-working) stands now:

        select *from (             select             y.*,             timestampdiff(minute, @prevdt, `time`) timedifference,             @prevdt := `time`             ddhistorical y,             (select @prevdt:=(select min(`time`) ddhistorical)) vars             id  = '". mysql_real_escape_string($id)."'             having timedifference > 16           ) s           order time desc           limit 25"; 

you need 2 levels of subquery:

select * (     select  y.*,             timestampdiff(minute, @prevdt, `time`) timedifference,             @prevdt := `time`             (select *                   ddhistorical                   id = '97'                   order `time`) y,                  (select @prevdt:=(select min(`time`) ddhistorical)) vars) z timedifference > 16 limit 25 

i'm not sure why outermost level needed. tried without (i.e. starting select y.*) , using having timedifference > 16. reason, reported single row timedifference = 45. if removed having clause, showed rows, gap reported 35 minutes. usually, there's no difference between

select ... having <cond> 

and

select * (select ...) <cond> 

i'm not sure why query violates rule -- suspect has user-variables.

edit:

i think i've figured out why didn't work having. mysql apparently evaluating condition calculates timedifference column each row, , discarding row. when timedifference <= 16, doesn't bother calculating rest of columns in select clause, never @prevdt := time. until gets past condition, it's comparing min(time).

moving timedifference check outer query forces calculate differences before filtering.

sqlfiddle