CODEWITHSUNDEEP
sqlpostgresql
demian85
demian85

Reputation: 2494

How to find if date is contained in a set of intervals?

I'm using PostgreSQL v13.

I need to find if a specific timestamp is included in a set of intervals. And of course, my idea is to do it in a single query instead of looping and building multiple queries.

So this query basically needs to merge the following statements for n intervals: <date> BETWEEN <start> AND <end>

Example of what I want (not a valid SQL):

SELECT * FROM rides r
WHERE device_id = 1 
AND timestamp IN (
       SELECT tstzrange(start_at, end_at)
       FROM segments 
       WHERE device_id = r.device_id
);

But of course the IN operator does not work that way.

Thanks.

Upvotes: 0

Views: 284

Answers (1)

nbk
nbk

Reputation: 49373

you can use EXISTS and range functions

SELECT * FROM rides r
WHERE device_id = 1 
AND EXISTS (SELECT 1 FROM segments 
       WHERE device_id = r.device_id 
       AND tstzrange(start_at, end_at) @> r.timestamp ::timestamp );

Upvotes: 2

Related Questions