CODEWITHSUNDEEP
sqldata-sciencedata-analysisprestosql-timestamp
Chris90
Chris90

Reputation: 1998

Joining on a timestamp within 30 seconds of each other?

I have a query where I am trying to join on a timestamp, but since there are not exact timestamps in one table and only every 30 seconds, can I join on that condition?

SELECT * FROM table loc
LEFT JOIN table dest ON loc.id = dest.id AND loc.timestamp = dest.timestamp

Except the second ON portion of the join I want it to be if the loc.timesamp = dest.timestamp if they are within 30 seconds of each other

Thanks

Upvotes: 0

Views: 848

Answers (2)

GMB
GMB

Reputation: 222542

Consider:

SELECT * 
FROM table loc
LEFT JOIN table dest 
    ON loc.id = dest.id 
    AND loc.timestamp 
        BETWEEN dest.timestamp - interval '30' second
        AND dest.timestamp + interval '30' second

While this will work; you need to consider the risk that multiple records would exist in the dest table with the joining interval. If this happens, then you would end up with duplicated loc records in the resultset. Depending on your data, this may or may not happen.

Upvotes: 4

Gordon Linoff
Gordon Linoff

Reputation: 1270181

You can express it as:

SELECT *
FROM table loc LEFT JOIN
     table dest
     ON loc.id = dest.id AND
        loc.timestamp >= dest.timestamp - INTERVAL '30' SECOND AND
        loc.timestamp <= dest.timestamp + INTERVAL '30' SECOND;

Upvotes: 4

Related Questions