I have the following postgresql 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 in date ranges such that for June 2003 it returns ID 5 and 6.
Use the containment operator <@:
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 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;
https://www.postgresql.org/docs/current/static/functions-range.html
use contains operator, eg:
postgres=# select '[2000-01-01,2017-01-01)'::daterange @> '2013.01.01'::date;
?column?
----------
t
(1 row)
so for you it would be smth like
select * from tbl where "Date" @> '2013.01.01'::date;