1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Time Variance Query - So close

Discussion in 'Databases' started by johnberman, Feb 18, 2017.

  1. #1
    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
    SEMrush
    John B
     
    johnberman, Feb 18, 2017 IP
    SEMrush
  2. Rukbat

    Rukbat Well-Known Member

    Messages:
    2,908
    Likes Received:
    37
    Best Answers:
    51
    Trophy Points:
    125
    #2
    ... 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).
     
    Rukbat, Feb 28, 2017 IP