Postgres query to fetch results between two dates

Question

This is example:

create table test (
id int,
name varchar(6),
start_date date,
end_date date
);


insert into test (id,name, start_date, end_date) values (1,'aaa', '2014-07-01', '2014-07-30');
insert into test (id,name, start_date, end_date) values (2,'bbb', '2014-07-01', '2014-08-30');
insert into test (id,name, start_date, end_date) values (3,'ccc', '2014-08-01', '2014-08-30');
insert into test (id,name, start_date, end_date) values (4,'ddd', '2014-08-16', '2014-08-30');
insert into test (id,name, start_date) values (5,'eee', '2014-07-01');
insert into test (id,name, start_date) values (6,'fff', '2014-08-16');

I need write query where result will be:

2;"bbb";"2014-07-01";"2014-08-30"
3;"ccc";"2014-08-01";"2014-08-30"
4;"ddd";"2014-08-16";"2014-08-30"
5;"eee";"2014-07-01";""
6;"fff";"2014-08-16";""

I've written this:

select * from test
where (start_date >= '2014-08-15' and (end_date <= current_Date or end_date is null)) or 
(start_date <= '2014-08-15' and end_date is null) ;

But I see only ddd,eee and fff records.

What did you expect ? Your WHERE clause is working as designed. — Jonathan M
– Jonathan M, Commented Sep 2, 2014 at 19:01

pozs · Accepted Answer · 2014-09-03 07:49:02Z

Instead of the compare operators, you can actually use PostgreSQL's own detection of date (& time) overlaping. There are actually 2 constructs:

1) The SQL compatible OVERLAPS operator:

(start1, end1) OVERLAPS (start2, end2)
(start1, length1) OVERLAPS (start2, length2)

This one does not handle well an open-ended date-period, but you can use the the special infinity date for that.

select *
from   test
where  (date '2014-08-15', current_date) overlaps
       (start_date, coalesce(end_date, date 'infinity'));

As far as I know, you can't use any index to speed up the query above.

Also:

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.

2) Using the daterange type:

Ranges can be unbounded, and you can explicitly set how to handle range bounds (to include them or not).

select *
from   test
where  daterange(start_date, end_date, '[]') &&
       daterange(date '2014-08-15', current_date, '[]');

Also, you can speed up this query, by applying a gist index on the daterange expression:

create index on test using gist ((daterange(start_date, end_date, '[]')));

SQLFiddle

Gordon Linoff · Accepted Answer · 2014-09-02 19:03:09Z

1

I am guessing (intelligently) that you want any period that overlaps with 2014-08-15 to the present date. If so, this logic returns what you want:

select *
from test
where (start_date >= '2014-08-15' and (end_date <= current_Date or end_date is null)) or 
      (start_date <= '2014-08-15' and (end_date > '2014-08-15' or end_date is null));

Here is a SQL Fiddle.

answered Sep 2, 2014 at 19:03

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

1 Comment

Rawness Over a year ago

Indeed ^^ You are my hero! Thanks a lot!

Collectives™ on Stack Overflow

Postgres query to fetch results between two dates

2 Answers 2

Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Related