Tricky Query that includes a time difference

john_berman

So this query is driving me a little crazy I have a table called quadrantids2017 which has a number of events submitted by different contributors, key fields are event_id which is unique and user_ID and datetime what I want is a query that will bring back all events which ocoured on the same date within 30 seconds of each other but different contributors so here is some data event ID, user_id, datetime 37775 2 2017-01-01 00:01:23 33500 1 2017-01-01 01:07:56 37776 2 2017-01-01 01:08:45 47827 3 2017-01-01 01:09:07 37777 2 2017-01-01 01:09:44 33501 1 2017-01-01 01:14:59 47828 3 2017-01-01 01:15:00 33502 1 2017-01-01 01:16:10 37778 2 2017-01-01 01:16:47 37779 2 2017-01-01 01:19:29 37780 2 2017-01-01 01:19:36 33503 1 2017-01-01 01:24:48 So i would want a result that looks like this 33500 1 2017-01-01 01:07:56 37776 2 2017-01-01 01:07:45 33501 1 2017-01-01 01:14:59 47828 3 2017-01-01 01:15:00 37780 2 2017-01-01 01:19:36 33503 1 2017-01-01 01:19:48 On issue I have is that my current attempts bring back matches from the same contributor Help really appreciated Regards John B

Lost User

Can you post your query and explain what records are returned?

john_berman

ok my query is like this

SELECT
b.event_id AS a,
b.user_ID AS a,
b.date AS a,
b.Time AS a
FROM quadrantids2017 AS a
JOIN quadrantids2017 AS b
WHERE a.`date` = b.`date`

AND time_to_sec(a.`Time`) - time_to_sec(b.`Time`) BETWEEN -30 AND 30

Note in this version I am using a separate date and time field here are some of the results I have spaced and marked what should have been returned 37775 2 2017-01-01 00:01:23 33500 1 2017-01-01 01:07:56 37776 2 2017-01-01 01:08:45 37776 2 2017-01-01 01:08:45 - correct 47827 3 2017-01-01 01:09:07 - correct 47827 3 2017-01-01 01:09:07 - correct 37777 2 2017-01-01 01:09:44 - correct 33501 1 2017-01-01 01:14:59 33501 1 2017-01-01 01:14:59 47828 3 2017-01-01 01:15:00 47828 3 2017-01-01 01:15:00 33502 1 2017-01-01 01:16:10 John B

Richard Deeming

Looks like you're missing:

AND a.event_id != b.event_id

from your WHERE clause. (You might want to change that to an ON clause for the JOIN.) If you want to exclude matches from the same user, you'll also need:

AND a.user_ID != b.user_ID

Or, to exclude matches for different users:

AND a.user_ID = b.user_ID

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

john_berman

:) so this works fine

SELECT
b.event_id AS a,
b.user_ID AS a,
b.date AS a,
b.Time AS a
FROM quadrantids2017 AS a
JOIN quadrantids2017 AS b
WHERE a.`date` = b.`date`
AND a.event_id != b.event_id
AND a.user_ID != b.user_ID
AND time_to_sec(a.`Time`) - time_to_sec(b.`Time`) BETWEEN -30 AND 30

my only query now is the length of time it took the record set its self has 3133 records and the query returned 2990 records but it took some 40 seconds to do ? - I understand its actually doing quite a lot John B

john_berman

spoke to soon, there are duplicates 37919 2 2017-01-01 11:43:02 37920 2 2017-01-01 11:43:23 47890 3 2017-01-01 11:42:56 47890 3 2017-01-01 11:42:56 47894 3 2017-01-01 12:13:31 33558 1 2017-01-01 12:13:29 47896 3 2017-01-01 13:24:44 37937 2 2017-01-01 13:24:17 37945 2 2017-01-01 14:09:53 37945 2 2017-01-01 14:09:53 sorry