Getting results between two dates in PostgreSQL

Question

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

Sylviasylviculture

@MarcoMariani I want it to be able to work just like in the example. – Sylviasylviculture

The question doesn't really say what "matching" means. Containing? Contained by? Overlapping? An exact match? – Kellie

Answer 1

 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.

Vocalism

It was not my question directly, however, using OVERLAPS saved my day. Thanks a lot :) – Stonedead

Answer 2

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. The manual:

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. See:

• Optimizing queries on a range of timestamps (two columns)

Flatways

Answer 3

just had the same question, and answered this way, if this could help.

select * 
from table
where start_date between '2012-01-01' and '2012-04-13'
or    end_date   between '2012-01-01' and '2012-04-13'

Ell

Answer 4

To have a query working in any locale settings, consider formatting the date yourself:

SELECT * 
  FROM testbed 
 WHERE start_date >= to_date('2012-01-01','YYYY-MM-DD')
   AND end_date <= to_date('2012-04-13','YYYY-MM-DD');

Knp

Answer 5

Looking at the dates for which it doesn't work -- those where the day is less than or equal to 12 -- I'm wondering whether it's parsing the dates as being in YYYY-DD-MM format?

Untouchability

Why not? This format depends on the clients' locale settings, database stores it in a couple of bytes, without any formatting. – Internationale

Answer 6

You have to use the date part fetching method:

SELECT * FROM testbed WHERE start_date  ::date >= to_date('2012-09-08' ,'YYYY-MM-DD') and date::date <= to_date('2012-10-09' ,'YYYY-MM-DD')

Nekton

Answer 7

No offense but to check for performance of sql I executed some of the above mentioned solutiona pgsql.

Let me share you Statistics of top 3 solution approaches that I come across.

1) Took : 1.58 MS Avg

2) Took : 2.87 MS Avg

3) Took : 3.95 MS Avg

Now try this :

 SELECT * FROM table WHERE DATE_TRUNC('day', date ) >= Start Date AND DATE_TRUNC('day', date ) <= End Date

Now this solution took : 1.61 Avg.

And best solution is 1st that suggested by marco-mariani

Catholicism

Answer 8

SELECT *
FROM ecs_table
WHERE (start_date, end_date) OVERLAPS ('2012-01-01'::DATE, '2012-04-12'::DATE + interval '1');

Shabuoth

Answer 9

Let's try range data type.

--sample data.

begin;
create temp table tbl(id  serial, user_id integer, start_date date, end_date date);
insert into tbl(user_id, start_date, end_date) values(1, '2012-04-09', '2012-04-13');
insert into tbl(user_id, start_date, end_date) values(1, '2012-01-09', '2012-04-12');
insert into tbl(user_id, start_date, end_date) values(1, '2012-02-09', '2012-04-10');
insert into tbl(user_id, start_date, end_date) values(1, '2012-04-09', '2012-04-10');
commit;

add a new daterange column.

begin;
alter table tbl add column tbl_period daterange ;
update tbl set tbl_period = daterange(start_date,end_date);
commit;

--now test time.

select * from tbl
    where tbl_period && daterange('2012-04-10' ::date, '2012-04-12'::date);

returns:

 id | user_id | start_date |  end_date  |       tbl_period
----+---------+------------+------------+-------------------------
  1 |       1 | 2012-04-09 | 2012-04-13 | [2012-04-09,2012-04-13)
  2 |       1 | 2012-01-09 | 2012-04-12 | [2012-01-09,2012-04-12)

further reference: https://www.postgresql.org/docs/current/functions-range.html#RANGE-OPERATORS-TABLE

Vargo