Getting results between two dates in PostgreSQL

35

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".

This question is tagged with sql postgresql date overlap

~ Asked on 2012-04-16 08:03:09

The Best Answer is


60

 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.

~ Answered on 2012-04-16 09:13:49


33

Assuming you want all "overlapping" time periods, i.e. all that have at least one day in common.

Try to envision time periods on a straight time line and move them around before your eyes and you will see the necessary conditions.

SELECT *
FROM   tbl
WHERE  start_date <= '2012-04-12'::date
AND    end_date   >= '2012-01-01'::date;

This is sometimes faster for me than OVERLAPS - which is the other good way to do it (as @Marco already provided).

Note the subtle difference (per documentation):

OVERLAPS automatically takes the earlier value of the pair as the start. Each time period is considered to represent the half-open interval start <= time < end, unless start and end are equal in which case it represents that single time instant. This means for instance that two time periods with only an endpoint in common do not overlap.

Bold emphasis mine.

Performance

For big tables the right index can help performance (a lot).

CREATE INDEX tbl_date_inverse_idx ON tbl(start_date, end_date DESC);

Possibly with another (leading) index column if you have additional selective conditions.

Note the inverse order of the two columns. Detailed explanation:

~ Answered on 2012-04-16 13:05:56


Most Viewed Questions: