❓ Consider the following table

ID  Date
 1  [2017-01-01,2051-01-01)
 2  [2017-01-01,2051-01-01)
 3  [2017-01-01,2051-01-01)
 4  [2017-01-01,2051-01-01)
 5  [2000-01-01,2017-01-01)
 6  [2000-01-01,2017-01-01)
 7  [2017-01-01,2051-01-01)
 8  [2017-01-01,2051-01-01)
 9  [2017-01-01,2051-01-01)
 10 [2017-01-01,2051-01-01)

How can i query the data to check whether or not it contains a certain date?

For example, what if we wanna check which date pair in the above table contains the date '2003-06-01'

✔️ Answer

Use the containment operator <@:

--This is a common table expression
with my_table(id, dates) as (
values
    (1, '[2017-01-01,2051-01-01)'::daterange),
    (2, '[2017-01-01,2051-01-01)'),
    (3, '[2017-01-01,2051-01-01)'),
    (4, '[2017-01-01,2051-01-01)'),
    (5, '[2000-01-01,2017-01-01)'),
    (6, '[2000-01-01,2017-01-01)'),
    (7, '[2017-01-01,2051-01-01)'),
    (8, '[2017-01-01,2051-01-01)'),
    (9, '[2017-01-01,2051-01-01)'),
    (10, '[2017-01-01,2051-01-01)')
)

select *
from my_table
where '2003-06-01'::date <@ dates;

 id |          dates
----+-------------------------
  5 | [2000-01-01,2017-01-01)
  6 | [2000-01-01,2017-01-01)
(2 rows)

Read more about Range Functions and Operators.

You can also check whether a date range (not a single date) is contained by dates:

where daterange('2003-01-01', '2003-12-31') <@ dates;

or whether a date range overlaps dates:

where daterange('2003-01-01', '2003-12-31') && dates;