Getting date list in a range in PostgreSQL

Javi picture Javi · Jul 9, 2012 · Viewed 52.7k times · Source

I'd like to get the list of days between the two dates (including them) in a PostgreSQL database. For example, if I had:

  • start date: 29 june 2012
  • end date: 3 july 2012

then the result should be:

29 june 2012
30 june 2012 
1 july 2012 
2 july 2012 
3 july 2012

What would be the best way of doing this in PostgreSQL?

Thanks.

Answer

maniek picture maniek · Jul 9, 2012
select CURRENT_DATE + i 
from generate_series(date '2012-06-29'- CURRENT_DATE, 
     date '2012-07-03' - CURRENT_DATE ) i

or even shorter:

select i::date from generate_series('2012-06-29', 
  '2012-07-03', '1 day'::interval) i