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.

Query to include + or - 5 seconds

Discussion in 'Databases' started by johnberman, Jun 29, 2020.

  1. #1
    Hi

    I have a table that records meteor events - each record amongst other things has the date and time. I want to search for an event and specify the date but also the time but i want to have a + or - on the time, i started with this
    SELECT
        duration_events.event_id,
        duration_events.date,
        duration_events.Time
    FROM
        duration_events
    WHERE
        duration_events.date = '2020-05-15'
    and
    duration_events.Time='05:22:30';
    Code (markup):
    but I want the search bring back events that are + or minus 5 seconds not just events that are at 05:22:30 and as I do lots of search for different events I dont want to use fixed between type statement - hope that makes sense
    SEMrush
    Help appreciated

    Regards
    John B
     
    Last edited by a moderator: Jun 30, 2020
    johnberman, Jun 29, 2020 IP
    SEMrush
  2. sarahk

    sarahk iTamer Staff

    Messages:
    26,125
    Likes Received:
    3,821
    Best Answers:
    108
    Trophy Points:
    665
    #2
    two approaches

    When you build your sql query inside a PHP/ASP/Java script you can calculate the dates using the date object
    or
    
    SELECT
        duration_events.event_id,
        duration_events.date,
        duration_events.time,
    FROM
        duration_events
    WHERE
        duration_events.date = '2020-06-02'
    AND duration_events.time BETWEEN DATE_ADD(CAST('05:10:00' AS TIME), INTERVAL -5 minute)
    AND DATE_ADD(CAST('05:10:00' AS TIME), INTERVAL 5 minute);
    Code (markup):
    I used minutes because it worked with my sample data, you'd just need to change the interval.
     
    sarahk, Jun 30, 2020 IP
  3. johnberman

    johnberman Greenhorn

    Messages:
    18
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    11
    #3
    Thank you thats appreciated

    John
     
    johnberman, Jun 30, 2020 IP