Results 1 to 3 of 3
  1. #1
    Join Date
    May 2014

    Unanswered: JOIN by approximate timestamp:


    I have two table generated from two source of data. due to this the timestamps are sometime not accurate with few missed entries or doubles in table2 whereas table1 is perfectly timestamped every second. I need to pull the combined data by time stamp, how do I achieve this is the approximate match to pick the nearest time-stamp.

    tb1> timestamp| value a|value b

    tb2 timestamp| value c|valued

    results expected if tb1.timestamp=tb2.timestamp(nearest positive value)|value1|value3|value3|value4

    Help me please

  2. #2
    Join Date
    Jun 2004
    Arizona, USA
    Hopefully, the column name really isn't timestamp, as this is a reserved word. (else you have to remember to always wrap the offending column names with apostrophes to avoid errors) - my reply is based on the assumption the columns are actually called 'ts'. (They should be named something a bit more descriptive anyway.)

    your query will be something along the lines of :
    select tb1.*, tb2.*
    from tb1
    join tb2 
       on tb1.ts >= tb2.ts and tb1.ts + interval '2 seconds' <= tb2.ts;
    You're probably going to have to play with the interval conversion in the join - look at the date-time conversion functions in the manual.
    Last edited by loquin; 08-26-14 at 14:28.
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert

  3. #3
    Join Date
    May 2014
    My data has entry for every second, will this not create a duplicate for few entries or will it only match +interval in absence of exact match?

Tags for this Thread

Posting Permissions

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