Mysql query to get rows with 5 minutes difference

Question

I have two tables which are linked with foreign key relationship. Both tables have Created Date columns. How can I find all matching rows where time difference is 5 minutes plus or minus.

What have you tried? See ask advice, please.

John Conde
– John Conde

2013-01-28 14:43:54 +00:00
Commented Jan 28, 2013 at 14:43 — John Conde
– John Conde, Commented Jan 28, 2013 at 14:43

Jerry B · Accepted Answer · 2013-01-28 14:50:08Z

1

You'll need to use the timestampdiff function and the abs function, something like this:

select * from table1 a, table2 b where a.ID=b.FOREIGNID and ABS(TIMESTAMPDIFF(MINUTE,a.CREATEDDATE,b.CREATEDDATE)) > 5

Documentation for both functions:

http://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html#function_abs

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_timestampdiff

answered Jan 28, 2013 at 14:50

Jerry B

1351 silver badge9 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Stefan · Accepted Answer · 2013-01-28 14:50:51Z

0

That should work. Or do you need it for both sides?

SELECT *
  FROM tableA a
  JOIN tableB b
    ON a.keyInfo = b.keyInfo
   AND b.created BETWEEN a.created - 5/(24*60) AND a.created + 5/(24*60)

answered Jan 28, 2013 at 14:50

Stefan

1062 bronze badges

1 Comment

Stefan Over a year ago

Then use Jerry´s answer (probably it has to be <= 5 at the end)

Collectives™ on Stack Overflow

Mysql query to get rows with 5 minutes difference

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related