Sql date select rows within X seconds range of each other

Question

Hello,

My sql table is something like (message,created)

I want to select those rows which are within X seconds from each other. Say the last message is within X seconds from NOW(), then it should select it. If the second last message is within X seconds from the last message then it should select it too. In other words each row should be compared with the next row and checked. For the last row it should be check with say NOW(). Basically I want the last session of messages (i.e. last set of messages that we linked to each other, assuming that consequtive messages within X seconds are linked to each other)

I have no idea how to go about writing an SQL query for this. Is it even possible?

Thank you very much for your time.

Answer 1

This script works in SQLServer. You should be able to take out the select statement and run it in MySQL.

DECLARE @Messages TABLE (Message VARCHAR(10), Created DATETIME)
DECLARE @Interval FLOAT

-- Interval is 1 day.
SET @Interval = 1

-- These should be in result
INSERT INTO @Messages VALUES ('Message1', GetDate())    
INSERT INTO @Messages VALUES ('Message2', GetDate()-1)
-- These should not be in result
INSERT INTO @Messages VALUES ('Message3', GetDate()-3)
INSERT INTO @Messages VALUES ('Message4', GetDate()-5)

SELECT m1.Message, m1.Created
FROM @Messages m1
     INNER JOIN @Messages m2 ON m2.Created <= m1.Created + @Interval                                
                                AND m2.Created >= m1.Created
                                AND m2.Message <> m1.Message
UNION ALL SELECT m2.Message, m2.Created
FROM @Messages m1
     INNER JOIN @Messages m2 ON m2.Created <= m1.Created + @Interval                                
                                AND m2.Created >= m1.Created
                                AND m2.Message <> m1.Message
ORDER BY Created

Answer 2

I believe you are thinking too complicated (But then again, maybe I misunderstood the requirement?)

This selects all messages created within 30 seconds before a particular message:

SELECT
  Id,
  MessageText,
  MessageDate
FROM
  Message
WHERE
  TIME_TO_SEC(TIMEDIFF(
    (
      SELECT MessageDate 
      FROM   Message
      WHERE  Id = 17
    ),
    MessageDate
  )) <= 30

where 17 is of course the message Id you are interested in, and 30 is the number of seconds in your time frame.