Postgres Pro
Downloads
Home   >   mailing lists

Thread: Sequence Of Dates ( Posting 2nd Time)

Sequence Of Dates ( Posting 2nd Time)

From
"Madhavi Daroor"
Date:
How do i generate a sequence of consecutive dates between 2 dates in a
single query?
For example......between '01-01-2003' and '01-04-2003' the result should be

01-01-2003
01-02-2003
01-03-2003
01-04-2003


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...


select (rownum+to_date('01-01-2000 00:00:00','mm-dd-yyyy hh24:mi:ss'))
timesheetdate
from skills having (rownum+to_date('01-01-2000 00:00:00','mm-dd-yyyy
hh24:mi:ss')) < to_date('01-01-2010 00:00:00','mm-dd-yyyy hh24:mi:ss')
group by rownum;




There is nothing like a rownum in postgres, so how can I do the same in
postgres?


Madhavi

Re: Sequence Of Dates ( Posting 2nd Time)

From
greg@turnstep.com
Date:
-----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-----

Re: Sequence Of Dates ( Posting 2nd Time)

From
Jean-Luc Lachance
Date:
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