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.

0
  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

    Help appreciated

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

    sarahk iTamer Staff

    Messages:
    28,500
    Likes Received:
    4,460
    Best Answers:
    123
    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:
    19
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    11
    #3
    Thank you thats appreciated

    John
     
    johnberman, Jun 30, 2020 IP