Getting results between two dates in PostgreSQL

sql postgresql date overlap
Psyche picture Psyche · Apr 16, 2012 · Viewed 106.2k times · Source

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

Marco Mariani picture Marco Mariani · Apr 16, 2012 
 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.

Related questions

How to compare dates in datetime fields in Postgresql?

I have been facing a strange scenario when comparing dates in postgresql(version 9.2.4 in windows). I have a column in my table say update_date with type 'timestamp without timezone'. Client can search over this field with only date (i.…

Read More
Postgresql query between date ranges

I am trying to query my postgresql db to return results where a date is in certain month and year. In other words I would like all the values for a month-year. The only way i've been able to do …

Read More
PostgreSQL: how to convert from Unix epoch to date?

The statement gives me the date and time. How could I modify the statement so that it returns only the date (and not the time)? SELECT to_timestamp( TRUNC( CAST( epoch_ms AS bigint ) / 1000 ) );

Read More