Mysql Select all rows with a difference of 10 minutes

Question

I have a table with a timestamp, and I want to get all the records with a difference of 10 minutes between each other. I will give you an example: I have 4 dates in my database:

01-12-2015 15:45:10
01-12-2015 15:55:10
01-12-2015 15:25:10
01-12-2015 15:15:10

I want to get only 2 dates that have a difference of 10 minutes between each other.

I do not want to receive all records where the timestamp is within 10 minutes of the current timestamp. I want to select all records that have a difference of 10 minutes between them. So in my example, I want to select dates:

01-12-2015 15:45:10
01-12-2015 15:55:10

These 2 dates should have a difference of 10 minutes between them. How can i do this?

Answer 1

You can get the data from the SQL query and write the logic to your php file as follows:

$select =   mysql_query("SELECT your_datetime_column_name as time,UNIX_TIMESTAMP(your_datetime_column_name) as time_string FROM your_table_name");
 $result = mysql_fetch_array($select);
    $final_result= NULL;
    $j=0;
    foreach($result as $ff)
    {
       foreach($result as $ff11)
    {
       if(abs($ff11['time_string'] - $ff['time_string']) ==600 ){ 
       $final_result[$j]['time1']=$ff['time'];
           $final_result[$j]['time2']=$ff11['time'];
       $j++;
        }
    }
    }

$final_result 10 . , .

Answer 2

now() . :

select t.*
from t
where t >= date_sub(now(), interval 10 minute);

Answer 3

, , 10 . 1- . SELECT TIMEDIFF('2015-12-11 08:20:00','2015-12-11 08:10:00')

Answer 4

, . JOIN index = index + 1 → . diff 10 .

SELECT *
FROM dates AS a LEFT OUTER JOIN dates AS b 
ON a.id = b.id+1
WHERE a.timestamps-b.timestamps >= 600

: http://sqlfiddle.com/#!9/31b0eb/3

Answer 5

Try:

Select * from a where a.id not in
 (select b.id from a b where TIMEstampDIFF(SECOND,a.mydate,b.mydate)<=600)
and a.id not in
 (select c.id from a c where TIMEstampDIFF(SECOND,a.mydate,c.mydate)>600)