Getting results between two dates in PostgreSQL including those two dates

Question

I have two dates , I want to display all records in those two dates including those dates.

How can i write query in PostgreSQL,

I have found

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

Here both days excluded

also

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

here start_date excluded.

what type do you have in your start_date and end_date columns? — roman
– roman, Commented Aug 19, 2013 at 9:54

Raphaël Althaus · Accepted Answer · 2013-08-19 09:56:28Z

1

you may use

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

or

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

because a timestamp has minutes, seconds... values, which "exclude" your datas.

Casting start_date and end_date to a date type (or using date_trunc function) should also work with OVERLAPS.

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

answered Aug 19, 2013 at 9:56

Raphaël Althaus

60.6k6 gold badges106 silver badges127 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

roman · Accepted Answer · 2013-08-19 11:35:39Z

1

use end_date + 1 day and < instead of <=

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

I think it should perform better than casting data in you table to date

answered Aug 19, 2013 at 9:53

roman

118k30 gold badges205 silver badges209 bronze badges