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


Comments

Post a Comment

Popular posts from this blog

Line ending issue with Mercurial or Visual Studio -

-
i seem have strange issue caused either visual studio or mercurial. localised single project, i'm guessing in project configuration causing issue. at difficult specify point, when perform action in visual studio, update every line ending in given file, means when @ file in sourcetree, every line has changed. i can replicate using nuget add package dependency; packages.config entirely replaced. if commit changes, including line endings, later same issue occur. workaround have shelve changes , reapply them, whereupon lines written correctly. i'm not sure application blame here. did make mistake of allowing sourcetree change mercurial configuration, issue have fixed, i'm not sure if has persisted. i thought might issue mercurial.ini files or hgrcs don't seem contain untoward. here's mercurial.ini [ui] username = .... [auth] bb.prefix = https://bitbucket.org/ bb.username = .... bb.password = .... [extensions] mq = rebase = [web] allow_push = * push_s...
Read more

java - Jtable duplicate Rows -

-
Image
when add new rows in jtable duplicate rows many times this method update jtable public final void tableaucomande(){ ((defaulttablemodel)tabcommande.getmodel()).getdatavector().clear(); session s=hibernateutil.getsessionfactory().opensession(); criteria cr1= s.createcriteria(commande.class); list <commande> lcs=cr1.list() ; criteria cr2= s.createcriteria(lignecommande.class); list <lignecommande> lck=cr2.list() ; if(lcs.size()!=0 && lck.size()!=0) { (commande lc:lcs) { for(lignecommande lig:lck){ ((defaulttablemodel)tabcommande.getmodel()).addrow(new object[]{ lc.getid_cmd(),lc.getnumcmd(),lc.getdate_cmd(),lc.getclient().getnomprenom(), lig.getproduit().getlib_prd(),lig.getquantite(),lig.getproduit().getid_prd() }); } } // tabcommande.setrowselectioninterval(tabcommande.getrowcount()-1,tabcommande.getrowcount()-1); } } this code add add data rows displaye...
Read more

java - Run a .jar on Heroku -

-
i trying run .jar file on heroku, , can't find instructions anywhere on how this. have read wants me compile uploading heroku. possible, , if is, how this? if helps, trying run minecraft craftbukkit server, , don't have pom.xml file yet. you didn't provide details i'll try give , people reaches question way it. first of all, create heroku account , create heroku application (assumed have done that). second, need generate application jar , upload heroku. so, open terminal , install heroku cli (assuming unix terminal): $ sudo npm install -g heroku-cli you need have npm , nodejs installed, can check link: https://www.digitalocean.com/community/tutorials/how-to-install-node-js-on-ubuntu-16-04 once done that, can use npm update node version acceptable heroku-cli: $ sudo npm install -g n $ sudo n 7.10.0 and run: $ sudo npm install -g heroku-cli in order deploy jars, need install deploy plugin: $ sudo heroku plugins:install heroku-cli-deploy ...
Read more