4

Is it possible to construct an on-the-fly tsrange and check if a value is within that?

I have a table that has two fields, session_start and session_end and I want to run a query that checks if a passed value is between those two values. Something along these lines (not valid SQL!):

SELECT pk FROM sessions WHERE ? IN [session_start, session_end)

I am unsure about the exact syntex for a literal tsrange and also how to check for inclusion (IN does not seem to work`.

Note that both the passed value as well as session_start and session_end are TIMESTAMP fields / values.

Cheers!

Christian P.
  • 4,784
  • 7
  • 53
  • 70

1 Answers1

6

Yes, you can use a range constructor for that:

SELECT rangetype(lower, upper[, bounds]);
-- bounds can be '[]', '[)' (default), '(]', '()'

-- in your case:
SELECT pk FROM sessions WHERE ? <@ tsrange(session_start, session_end, '[)');

Note: the containment operator is <@ for ranges (not IN).

pozs
  • 34,608
  • 5
  • 57
  • 63