CODEWITHSUNDEEP
mysql
minas
minas

Reputation: 1581

Mysql select records having difference of hours

I have a table:

ID  DATE

1   2013-08-12 08:59:16
2   2013-08-13 08:59:16
3   2013-08-14 08:59:16
4   2013-08-17 08:59:16

I want to select those records where difference of dates is within 36 hours of each other, not the current date

In the example above, the dates on the 12th and 13th is one case and 13th and 14th is another.

Sorry if question not clear, but its for purely mysql

Upvotes: 2

Views: 306

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269603

You can use a correlated subquery for this. The following gets any row within 36 hours of the preceding or next row:

select t.*
from t
where exists (select 1
              from t t2
              where t2.id <> t.id and
                    t2.date between t.date - interval 36 hour and t.date + interval 36 hour
             );

Upvotes: 3

fthiella
fthiella

Reputation: 49049

You could use a query like this:

SELECT
  t1.ID id1,
  t1.`date` date1,
  t2.ID id2,
  t2.`date` date2
FROM
  tablename t1 INNER JOIN tablename t2
  ON
    t1.id!=t2.id AND
    t2.`date` BETWEEN t1.`date` AND t1.`date`+ INTERVAL 36 HOUR

Please see fiddle here.

Upvotes: 2

Martijn
Martijn

Reputation: 16103

You could do something like this (small edit: OP mentioned the sql-only part after my answer):

$result = $connection->query("SELECT id,date FROM tablename");

while($thisRow= $result->fetch_assoc() ){
    $nextQuery = "SELECT id,date FROM tablename 
                  WHERE date>".$thisRow['date']." AND date<=".strtotime($thisRow['date']."+ 1 day")."
                  LIMIT 1";
    $next= connection->query($nextQuery);
    if($next->num_rows!==0){
        /* This line and the next line match
           $thisRow has info about current line
           $fetchNext has info about next line */
        $fetchNext = $next->fetch_assoc();
    }
}

Upvotes: 0

Related Questions