CODEWITHSUNDEEP
phpmysqldate
Gearbox
Gearbox

Reputation: 64

find dates of data gaps from mysql db with php

I have a db with a few tables with data recordings with timestamps as key (dateGMT). I am trying to create a function that returns an array of 1 and 0, with 0 for every day where there is a gap in the data of 4 hours or more.

For some reason my function won't work, I think it is related to when searching for the number of the day i need to mark with 0. Anyone able to see where i might have made my mistake?

Also, this does not feel too efficient for some reason, other ways to solve the original task are also welcome!

Thanks in advance! 

//create array with the dates from start of recordings to now
$period = iterator_to_array(new DatePeriod(new DateTime('2013-06-10'),new DateInterval('P1D'),new DateTime()));
$p2 = array();
$n = 0;
//the actual date is used as key for the number of the date
foreach($period as $p){
  array_push($p2,date('Y-m-d',strtotime($p->format('Y-m-d'))));
  //other way i tried: $p2[$p->format('Y-m-d')]= $n; $n++;
}

function makeArr($table,$p) {
  $con = mysql_connect("127.0.0.1", "user", "pass");
  mysql_select_db("table",$con);

  $ret = array_pad(array(),count($p),0);
  $query = "SELECT dateGMT FROM `$table` ORDER BY `dateGMT` ASC";
  $result = mysql_query($query);

  $d1 = strtotime('2013-06-10');
  $n = 0;
  while ($row = mysql_fetch_assoc($result, MYSQL_ASSOC)){
    $d2 = strtotime(implode("",$row));
    if($d1+14400 > $d2){
      $ret[array_search(date('Y-m-d',$d1),$p)] = 1;
      //part of the other way i tried: $ret[$p[$d1]] = 1; 
    }
    $d1 = $d2;
  }
  return $ret;
}

Upvotes: 2

Views: 439

Answers (2)

Alden W.
Alden W.

Reputation: 1439

One way that would be very efficient to do this would be to write a stored procedure using cursors. I think this would be quicker than the subqueries in the other answers. This procedure creates a cursor and iterates it through the datetime records that you are checking. If there is ever a gap of more than what you specify it will write a 1, if not it writes a 0 to a table.

    CREATE PROCEDURE findgaps()
    BEGIN    
    DECLARE done INT DEFAULT FALSE;
    DECLARE a,b DATETIME;
    DECLARE cur CURSOR FOR SELECT dateTimeCol FROM targetTable
                           ORDER BY dateTimeCol ASC;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;     
    OPEN cur;       
    FETCH cur INTO a;       
    read_loop: LOOP
        SET b = a;
        FETCH cur INTO a;   
        IF done THEN
            LEAVE read_loop;
        END IF;     
        IF DATEDIFF(a,b) > 1 THEN
            INSERT INTO tmp_table (date, gap)
            VALUES (DATE(a),1)
            ON DUPLICATE KEY UPDATE gap = 1;
        ELSE
            INSERT IGNORE INTO tmp_table (date, gap)
            VALUES (DATE(a),0);
        END IF;
    END LOOP;           
    CLOSE cur;      
    END;

In this case it is assumed that 'tmp_table' exists, and has a primary key of 'date' which is a DATE var type. You could easily define this as a TEMPORARY table in the procedure, but I left it out of this example.

What I imagine you would want to do is to write this as a function rather than a procedure and return the result set.

Upvotes: 0

Hernandcb
Hernandcb

Reputation: 539

There are several ways to find the gaps using a sql query, check this post: Method of finding gaps in time series data in MySQL?

Upvotes: 1

Related Questions