Forum OpenACS Development: How to select all dates on Postgres?

Collapse
1: How to select all dates on Postgres?
Posted by Jade Rubick on 01/09/07 05:57 PM
Hi all:

In Oracle, there are a few tricks you can use to dynamically select all the date values between two dates.

Does anyone know how to do this in Postgres?

I'd like to do an outer join against all dates between two ranges, so that I can graph values that include dates with data and those without.

Any ideas?

Thanks,
Jade

Collapse
2: Re: How to select all dates on Postgres? (response to 1)
Posted by Jade Rubick on 01/09/07 05:58 PM
Here's one example of one way to do it in Oracle:
http://halisway.blogspot.com/2006/12/date-range-generation-in-oracle.html

I've seen a lot of others as well -- Tom Kyte posted on this I believe.

Collapse
3: Re: How to select all dates on Postgres? (response to 1)
Posted by Steve Manning on 01/10/07 10:14 AM

Jade

If its a Postgres 8+ then its trivial using the generate_series() function  see http://www.postgresql.org/docs/8.0/interactive/functions-srf.html

e.g 

select current_date + s.a as dates from generate_series(0,14,7) as s(a);
   dates
------------
 2004-02-05
 2004-02-12
 2004-02-19

(3 rows)

 I don't think this function was available before 8 but it may be possible to hand craft your own version.

 
    - Steve