Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2004

    Unanswered: "Nearest time" cross-reference question

    Hi all,

    I have a problem cross-referencing two tables by time

    My tables have TimeStamp-type columns but there are not exact matches
    for instance, a record in table1 might look like:


    and two records in Table2 might look like


    i need a query that recognizes that:
    a) the time difference between table1/record1 and table2/record1 is 2 seconds
    b) the time difference between table1/record1 and table2/record2 is 10 seconds

    and matches the data from the records with the smallest time difference
    so result of query = data1,data2,data3,data4

    thanks, hope that's clear

  2. #2
    Join Date
    Nov 2004
    I assume that you know the time you're working with. Let's name that time $time
    now we need two variables for +-10sec and two for +- 2sec
    $maxtime2 = $time + 2
    $mintime2 = $time - 2

    $maxtime10 = $time + 10
    $mintime10 = $time - 10

    now we can query our db:

    SELECT * from table1, table2 WHERE table1.time BETWEEN $maxtime2 AND $mintime2 AND table2.time BETWEEN $maxtime10 AND $mintime10

    Hope this helps

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts