0

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') 
                                               )
4
  • Please have a look at this questions related to PostgreSQL and time intervals. dba.stackexchange.com/search?q=%5Bpostgresql%5D+time+interval
    – McNets
    Sep 23 at 18:00
  • date_bin from PostgreSQL v14 will make that simple. Sep 24 at 5:30
  • @LaurenzAlbe - 14 is still in beta (as of 24/09/2021). I don't know about you, but I wouldn't be recommending beta software to my boss/organisation/company?
    – Vérace
    Sep 24 at 6:01
  • @Vérace it will be released very soon, and I would have no problem recommending RC1 to my boss. Sep 24 at 6:42
0

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.