0

So for example, if I had two tables, communication and movement that both have a columns named ID and timestamp, and I had a query that generally looked something like this: 

SELECT * FROM movement m
JOIN communication c 
ON m.ID = c.ID 
WHERE m.timestamp <= c.timestamp
ORDER BY abs(TIMESTAMPDIFF(second,ctstamp,m.timestamp))
LIMIT 1;

This finds the closest communication timestamp for a given movement timestamp when m.timestamp <= c.timestamp, that is when the movement timestamp is less than the communication timestamp. But that leaves out all of m.timestamp >= c.timestamp.

What I want to do is create a range of like 10 seconds on either side, so that it would be: 

WHERE m.timestamp BETWEEN c.timestamp-10 secs AND c.timestamp+10 secs

so if the communication timestamp was '2012-03-02|09:02:30', then it would be:

WHERE m.timestamp BETWEEN '2012-03-02|09:02:20' AND '2012-03-02|09:02:40'

So how would I implement this?

Any help would be greatly appreciated, thanks!!

EDIT

In the end, I used this:

WHERE m.timestamp BETWEEN TIMESTAMPADD(SECOND,-10,c.timestamp) 
AND TIMESTAMPADD(SECOND,10,c.timestamp);
ocean800
  • 3,489
  • 13
  • 41
  • 73

1 Answers1

2

Just use the interval keyword:

WHERE m.timestamp BETWEEN c.timestamp - interval 10 second AND
                          c.timestamp + interval 10 second
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks! I would need to user `interval` in conjunction with [`timestampadd()`](https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_timestampadd), right? Kind of like what was in [this](http://stackoverflow.com/questions/2793619/working-with-interval-and-curdate-in-mysql) question? – ocean800 Jun 11 '15 at 19:55