MySQL事件历史记录，在前一个5分钟内找到每一行，直到找不到行

Question

I have a database containing a table of objects (network devices) and an event table, containing timestamps of when they couldn't be reached via ping. The table does not have any information on when they COULD be pinged, only when they were unreachable.

To calculate "down time" I made a php script that gets any even that is within n seconds of the previous event row for that object. The script ends up being ~50-100 separate queries, and I'm not sure how to fit this into one SQL query.

Any help is appreciated!

function GetDownTime(PDO $sql, $Objid) {
    $DownChain=array();
    $Last=null;
    $i=1;
    while($Last!==False) {
        try {
            // Select the down timestamp for the correct object
            // where down is LESS THAN the last down pulled (or current time if this is the first attempt)
            // where down is not greater than or equal to expired
            // expired is the last timestamp minus 600 (10 minutes).  -  this means we wont grab timestamps after a 10 minute gap (uptime)
            $GetEvent=$sql->Prepare("SELECT down,UNIX_TIMESTAMP(down) as unix FROM events
                                            WHERE obj_id=:objid
                                            AND UNIX_TIMESTAMP(down) < :last
                                            AND UNIX_TIMESTAMP(down) >= :expired
                                        ORDER BY down DESC
                                        LIMIT 1");
            $GetEvent->Execute(array(
                 ':objid'   =>  $Objid
                ,':last'    =>  ($Last ? $Last : time())
                ,':expired' =>  $Last-(DOWNTIME_MAXHOPSIZE ? DOWNTIME_MAXHOPSIZE : 600) // If for any reason the function can't get the constant value then default to 600
            ));
        } catch (PDOException $e) {
            die($e->getmessage());
        }

        if($GetEvent->RowCount()==1) {
            // Event found
            $Event=$GetEvent->Fetch(PDO::FETCH_ASSOC);
            $Last = $Event['unix'];
            $DownChain[] = $Event['unix'];
        } else {
            // Event not found
            $Last=false;
        }
        $i++;
    }

    /* etc etc */
}

Answer 1

This aligns with approach noted by @JohnMcMahon in comments. Don't limit the result set, just pull one row in at a time (i.e. iterate the DB rows), and stop your loop from reading out records once you find a record that breaks your condition. No need for complex query at all here.