Extension on an existing query

So i was helped before with this


I have a table of events (they are actually radio meteor events)
This table has amongst other things

event_id which is unique
Date
time: Event time is in a time field HH:MM:SS
contributor

in practical terms the event may be repeated several times for any given event as different contributors may have records the same event.

So im looking for a query that will return all records were the date and time match


and the answer was

select event_id, eventdate, eventtime, contributor_id
, (select count(event_id)
from rme as r2
where rme.eventdate = r2.eventdate
and rme.eventtime = r2.eventtime
group by rme.eventdate, rme.eventtime) as counter
from rme
having counter > 1



I want a query that will bring back where the date matches but the time can be plus or minus 30 seconds for a match

so imagine the following events were recorded on the 27 Jan 2017

02:35:00
02:35:15
02:36:00
02:38:00
02:39:15

I would want it to return

02:35:00
02:35:15


Hope this makes sense

John B

 

You don't really need any count. If I were you, I will do like this :

select eventtime
from rme R
where exists (select 1
from rme R2
where R.eventdate = r2.eventdate
and R.eventtime between DATE_ADD(r2.eventtime, INTERVAL -30 SECOND)
and DATE_ADD(r2.eventtime, INTERVAL +30 SECOND))

 

Sorry for the delay, i tried this but it failed , however I want to customise even further if possible ?

Now this works fine

SELECT
meteor_events.event_id AS event_id,
meteor_events.date AS date,
meteor_events.Time AS time,
(select count(`r2`.`event_id`) AS `count(event_id)`
from `meteor_events` `r2` where ((`meteor_events`.`date` = `r2`.`date`) and
(`meteor_events`.`Time` = `r2`.`Time`)) group by `meteor_events`.`date`,`meteor_events`.`Time`) AS counter
from `meteor_events`
having (`counter` > 1)
order by `meteor_events`.`Time`


I want to update it to do 2 things

1 - only bring back events with a specific date range
2 - were the time varies by 30 seconds as per the orginal question

Regards
John B

 

Hi John,
You need to add WHERE clause containing the filter criteria like date range
After FROM clause add

WHERE
meteor_events.date between @date1 and @date2

Code (SQL):