select time intervals from a nested query

Question

I have a table which has the following columns:

 id, start_timestamp, device_id

I am trying to get results of all start_timestamps within 5 seconds of a range of generated periodic timestamps.

For example, generating a series of timestamps every 80 seconds, I need to get all 'start_timestamps' that fall within 5 seconds of each of those timestamps.

So something like this query, but can't figure out the actual query. CTEs might be too exhaustive as the table has millions of records.

Appreciate any help, thanks!

SELECT *
FROM time_intersections 
WHERE  start_timestamp **within 5 seconds of each of these:** 
                                               (
                                                 SELECT *
                                                 FROM generate_series(timestamp '2021-03-07',
                                                                      timestamp '2021-08-16',
                                                                      interval  '80 sec') 
                                               )

Answer 1

Use a CTE (or subquery) to compute the bounds of the search, then join it to your data table to extract the data:

WITH search AS (
SELECT ts-interval '5 sec' AS lowbound, 
       ts+interval '5 sec' AS hibound
FROM 
        generate_series(timestamp '2021-03-07', timestamp '2021-08-16', interval  '80 sec') t(ts)
)
SELECT * FROM time_intersections
JOIN search ON (start_timestamp >= lowbound AND start_timestamp <= hibound)