select time intervals from a nested query

0 votes

1 answer

519 views

postgresql query postgresql-9.6 datetime time-series-database

                          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') 
	                                           )

                        

Asked by roy naufal (187 rep)

Sep 23, 2021, 04:13 PM
Last activity: Mar 16, 2025, 06:04 PM

select time intervals from a nested query

Related Questions