Psyche Psyche - 24 days ago 17
SQL Question

Getting results between two dates in PostgreSQL

I have the following table:

+-----------+-----------+------------+----------+
| id | user_id | start_date | end_date |
| (integer) | (integer) | (date) | (date) |
+-----------+-----------+------------+----------+


Fields
start_date
and
end_date
are holding date values like
YYYY-MM-DD
.

An entry from this table can look like this:
(1, 120, 2012-04-09, 2012-04-13)
.

I have to write a query that can fetch all the results matching a certain period.

The problem is that if I want to fetch results from
2012-01-01
to
2012-04-12
, I get 0 results even though there is an entry with
start_date = "2012-04-09"
and
end_date = "2012-04-13"
.

Answer
 SELECT *
   FROM mytable
  WHERE (start_date, end_date) OVERLAPS ('2012-01-01'::DATE, '2012-04-12'::DATE);

Datetime functions is the relevant section in the docs.

Comments