Thread: DATE SQL

I want a result like

'2006-03-01'
'2006-03-02'
'2006-03-03' 
'2006-03-04' 


FROM table xxx

……| start_date   |   end_date   |……
    | '2006-03-01' |  '2006-03-04'|

how can I write the sql

thanks

-----------------------------------------------
                         wanp2005@gmail.com

Well, in pure SQL I don't think you could do it without resorting to
ugly tricks like defining an all_days table that had one row per day in
your range, then doing something like, "select from all_days a join xxx
x where a.day >= x.start_date and a.day <= x.end_date"

So . . . don't do that.  What you want isn't really a pure relational
operation, so SQL isn't the right language for it.  Either do it in your
application code, or write a server-side function.

In either case, this is more of a pgsql-general question, since it's not
specific to JDBC at all.

-- Mark Lewis


On Mon, 2006-03-06 at 18:39 +0800, wanpeng wrote:
> I want a result like
>
> '2006-03-01'
> '2006-03-02'
> '2006-03-03'
> '2006-03-04'
>
>
> FROM table xxx
>
> ……| start_date   |   end_date   |……
>     | '2006-03-01' |  '2006-03-04'|
>
> how can I write the sql
>
> thanks
>
> -----------------------------------------------
>                          wanp2005@gmail.com
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings

On Mon, Mar 06, 2006 at 06:39:09PM +0800, wanpeng wrote:
> I want a result like
>
> '2006-03-01'
> '2006-03-02'
> '2006-03-03'
> '2006-03-04'
>
>
> FROM table xxx
>
> ¡­¡­| start_date   |   end_date   |¡­¡­
>     | '2006-03-01' |  '2006-03-04'|
>
> how can I write the sql

No problem if you're willing to use one of PostgreSQL's built-in
set-returning functions in your target list.

SELECT (
    start_date +
    generate_series(0,end_date-start_date) * interval '1 day'
    )::date AS "range"
FROM your_table;

Cheers,
D
--
David Fetter david@fetter.org http://fetter.org/
phone: +1 415 235 3778

Remember to vote!