Re: Sequence Of Dates ( Posting 2nd Time)

I am curious.  How is it done in Oracle?


greg@turnstep.com wrote:
>
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> > How do i generate a sequence of consecutive dates between 2 dates in a
> > single query?
> >
> > I don't want to use any pgsql or any othere procedural languag. The result
> > should be got in a single sql statement. Like in oracle I can do it as
> > follows by using one of my tables...
>
> Tough requirements! Asssume you have a sequence 's':
>
> CREATE SEQUENCE s;
>
> Assume you also have a table with enough rows to comfortably cover the
> number of dates you expect ot be returned. In this case, I'll use
> the system table pg_proc, which should have more than enough rows
> for this example.
>
> Given those two requirements, you could do this:
>
> SELECT TO_DATE(
>   (SELECT setval('s',TO_CHAR('01-01-2003'::timestamp,'J')::integer)),'J')
> UNION
> SELECT TO_DATE(nextval('s'),'J') FROM pg_proc
>   WHERE currval('s') < TO_CHAR('01-04-2003'::timestamp,'J')::integer;
>
> To return the dates in the format you want (Month-Day-Year):
>
> SELECT TO_CHAR(TO_DATE(
>   (SELECT setval('s', TO_CHAR('01-01-2003'::timestamp, 'J')::integer)), 'J'),
>   'MM-DD-YYYY')
> UNION
> SELECT TO_CHAR(TO_DATE(nextval('s'), 'J'),'MM-DD-YYYY') FROM pg_proc
>   WHERE currval('s') < TO_CHAR('01-04-2003'::timestamp,'J')::integer;
>
> Far better would be to use whatever language your application is written in: most
> have good interation and date-manipulation features which would get the info
> much more easily.
>
> - --
> Greg Sabino Mullane greg@turnstep.com
> PGP Key: 0x14964AC8 200303061000
>
> -----BEGIN PGP SIGNATURE-----
> Comment: http://www.turnstep.com/pgp.html
>
> iD8DBQE+Z2Q6vJuQZxSWSsgRAmZ9AKDeWJS7NS+grseOqTK9hyBfSxn+iACg1KuN
> JuHJoqO773GA0+4zSENO+KU=
> =Cosf
> -----END PGP SIGNATURE-----
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org