Getting date list in a range in PostgreSQL

Question

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 1

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

Answer 2

As timestamp:

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

    generate_series     
------------------------
 2012-06-29 00:00:00-03
 2012-06-30 00:00:00-03
 2012-07-01 00:00:00-03
 2012-07-02 00:00:00-03
 2012-07-03 00:00:00-03

or casted to date:

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

 generate_series 
-----------------
 2012-06-29
 2012-06-30
 2012-07-01
 2012-07-02
 2012-07-03

Answer 3

This should do it:

select date '2012-06-29' + i
from generate_series(1, (select date '2012-07-3' - date '2012-06-29')) i

If you don't want to repeat the start_date in the subselect things get a bit more complicated:

with min_max (start_date, end_date) as (
   values (date '2012-06-29', date '2012-07-3')
), date_range as (
  select end_date - start_date as duration
  from min_max
)
select start_date + i
from min_max
  cross join generate_series(1, (select duration from date_range)) i;

(See maniek's answer for a much better version of the "no-repeat" problem)

Answer 4

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

Answer 5

For things like this its generally handy to have a dates table in the system.

Just like a numbers table they can be very useful and quicker to use than generating the dates on the fly, especially when you scale up to large data sets.

Such a date table from 1900 to 2100 will be very small, so there isn't much over head in storage.

Edit: Dunno why this is getting voted down, it will probably be the best for performance. Plus it has so many other advantages. Want to link orders to a an quarters performance numbers? Its a simple link between the tables. (Order.OrderDate -> Dates.Date -> Dates.Quarter -> PerformanceTotal.Quarter) etc. Its the same for dealing with working days, like the last working day of a month, or the first Tuesday of the previous month. Like a numbers table, I'd strongly recommend them!