My database contains a table which stores meteor events submitted by different contributors each event has a unique ID and other things stored are the date and time and contributor ID There can be many events on the same date and time submitted by different contributors, I want to write a query where it would bring back all records which match by date and time however as recording equipment may not by time synchronized accurately I wanted to be able to have a variance of plus or minus 30 seconds SELECT a.event_id, a.user_ID, a.`date`, a.`Time` FROM quadrantids_date AS a JOIN quadrantids_date AS b WHERE a.event_id != b.event_id AND a.`date` = b.`date` AND time_to_sec(a.`Time`) - time_to_sec(b.`Time`) BETWEEN -30 AND 30; sample data shows the following (event ID, Contributor, Date, time) 37776 2 2017-01-01 01:08:45 47827 3 2017-01-01 01:09:07 33501 1 2017-01-01 01:14:59 47828 3 2017-01-01 01:15:00 37779 2 2017-01-01 01:19:29 37780 2 2017-01-01 01:19:36 33503 1 2017-01-01 01:24:48 33503 1 2017-01-01 01:24:48 event 37779 and 37780 are within the 30 second rule but they are from the same contributor, ie contributor number 2 - Somehow these need to be removed also event 33503 its duplicated for some reason - in the main table events are unique so there is only 33505 - really dont understand why this is here Pointers really appreciated John B
... AND time_to_sec BETWEEN (CONVERT('01:08:45' USING TIME) AND CONVERT('01:24:48' USING TIME)); should work. You'll have to determine the min and max times (and account for 00:00:00 and 12:00:00 crossovers).