From middink@indo.net.id Fri Jan  28 14:27:42 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 8588E3A62C3
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 28 Jan 2005 14:27:42 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 55740-07
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 28 Jan 2005 14:27:29 +0000 (GMT)
Received: from smtp-1.indo.net.id (smtp-1.indo.net.id [202.159.32.91])
	by svr1.postgresql.org (Postfix) with SMTP id 17CFD3A6291
	for <pgsql-sql@postgresql.org>; Fri, 28 Jan 2005 14:27:28 +0000 (GMT)
Received: (qmail 1001 invoked from network); 28 Jan 2005 21:27:25 +0700
Received: from smtp.indo.net.id (202.159.32.81)
	by smtp-1.indo.net.id with SMTP; 28 Jan 2005 21:27:25 +0700
Received: (qmail 7495 invoked from network); 28 Jan 2005 21:27:21 +0700
Received: from unknown (HELO middinks) (202.159.122.104)
	by smtp.indo.net.id with SMTP; 28 Jan 2005 21:27:21 +0700
Message-ID: <03a001c50545$6bd942d0$1f00a8c0@middinks>
From: "Muhyiddin A.M Hayat" <middink@indo.net.id>
To: <pgsql-sql@postgresql.org>
Subject: Calendar Function
Date: Fri, 28 Jan 2005 22:26:48 +0800
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_039D_01C50588.750FD470"
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2900.2180
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.431 tagged_above=0 required=5 tests=AWL,
	DNS_FROM_RFC_WHOIS, HTML_60_70, HTML_MESSAGE
X-Spam-Level: 
X-Archive-Number: 200501/306
X-Sequence-Number: 20094

This is a multi-part message in MIME format.

------=_NextPart_000_039D_01C50588.750FD470
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Dear All,

How to create Calendar Function or Query. I would like to display date =
form yyyy-mm-dd to yyyy-mm-dd or display date in one Month

e.g:

    date
------------
 2005-01-01
 2005-01-02
 2005-01-03
 2005-01-04
 2005-01-05
 2005-01-06
 2005-01-07
 2005-01-08
 2005-01-09
 2005-01-10
 2005-01-11
 2005-01-12
 2005-01-13
 2005-01-14
 2005-01-15
 2005-01-16
 2005-01-17
 2005-01-18
 2005-01-19
 2005-01-20
 2005-01-21
 2005-01-22
 2005-01-23
 2005-01-24
 2005-01-25
 2005-01-26
 2005-01-27
 2005-01-28
 2005-01-29
 2005-01-30
 2005-01-31


------=_NextPart_000_039D_01C50588.750FD470
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2900.2523" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>Dear All,</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>How to create Calendar Function or =
Query. I would=20
like to display date form yyyy-mm-dd to yyyy-mm-dd or display date in =
one=20
Month</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>e.g:</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>&nbsp;&nbsp;&nbsp;=20
date<BR>------------<BR>&nbsp;2005-01-01<BR>&nbsp;2005-01-02<BR>&nbsp;200=
5-01-03<BR>&nbsp;2005-01-04<BR>&nbsp;2005-01-05<BR>&nbsp;2005-01-06<BR>&n=
bsp;2005-01-07<BR>&nbsp;2005-01-08<BR>&nbsp;2005-01-09<BR>&nbsp;2005-01-1=
0<BR>&nbsp;2005-01-11<BR>&nbsp;2005-01-12<BR>&nbsp;2005-01-13<BR>&nbsp;20=
05-01-14<BR>&nbsp;2005-01-15<BR>&nbsp;2005-01-16<BR>&nbsp;2005-01-17<BR>&=
nbsp;2005-01-18<BR>&nbsp;2005-01-19<BR>&nbsp;2005-01-20<BR>&nbsp;2005-01-=
21<BR>&nbsp;2005-01-22<BR>&nbsp;2005-01-23<BR>&nbsp;2005-01-24<BR>&nbsp;2=
005-01-25<BR>&nbsp;2005-01-26<BR>&nbsp;2005-01-27<BR>&nbsp;2005-01-28<BR>=
&nbsp;2005-01-29<BR>&nbsp;2005-01-30<BR>&nbsp;2005-01-31</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV></BODY></HTML>

------=_NextPart_000_039D_01C50588.750FD470--





From franco@akyasociados.com.ar Fri Jan  28 15:47:24 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 1052A3A62FA
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 28 Jan 2005 15:47:24 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 64137-07
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 28 Jan 2005 15:47:13 +0000 (GMT)
Received: from lucifer.oficina (unknown [200.68.121.201])
	by svr1.postgresql.org (Postfix) with ESMTP id 306943A6307
	for <pgsql-sql@postgresql.org>; Fri, 28 Jan 2005 15:47:11 +0000 (GMT)
Received: from localhost (localhost.akyasociados.com.ar [127.0.0.1])
	by lucifer.oficina (8.13.1/8.12.10) with ESMTP id j0SFkq49093761;
	Fri, 28 Jan 2005 12:46:52 -0300 (ART)
	(envelope-from franco@akyasociados.com.ar)
Received: from lucifer.oficina ([127.0.0.1])
	by localhost (lucifer.oficina [127.0.0.1]) (amavisd-new, port 10024)
	with LMTP id 92442-10; Fri, 28 Jan 2005 12:46:51 -0300 (ART)
Received: from [192.168.1.168] (taz.oficina [192.168.1.168])
	(authenticated bits=0)
	by lucifer.oficina (8.13.1/8.12.10) with ESMTP id j0SFkoK0093758;
	Fri, 28 Jan 2005 12:46:50 -0300 (ART)
	(envelope-from franco@akyasociados.com.ar)
Message-ID: <41FA5E6A.50002@akyasociados.com.ar>
Date: Fri, 28 Jan 2005 12:46:50 -0300
From: Franco Bruno Borghesi <franco@akyasociados.com.ar>
User-Agent: Mozilla Thunderbird 1.0 (X11/20041216)
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: "Muhyiddin A.M Hayat" <middink@indo.net.id>
Cc: pgsql-sql@postgresql.org
Subject: Re: Calendar Function
References: <03a001c50545$6bd942d0$1f00a8c0@middinks>
In-Reply-To: <03a001c50545$6bd942d0$1f00a8c0@middinks>
Content-Type: multipart/alternative;
	boundary="------------000502020909030806010808"
X-Virus-Scanned: by amavisd-new at akyasociados.com.ar
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.202 tagged_above=0 required=5 tests=AWL, HTML_60_70,
	HTML_MESSAGE, HTML_TITLE_EMPTY, RCVD_IN_SORBS_DUL
X-Spam-Level: 
X-Archive-Number: 200501/310
X-Sequence-Number: 20098

This is a multi-part message in MIME format.
--------------000502020909030806010808
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

maybe somthing like this:

CREATE OR REPLACE FUNCTION calendar (DATE, DATE) RETURNS SETOF DATE 
LANGUAGE 'plpgsql' AS '
DECLARE
    v_from ALIAS FOR $1;
    v_to ALIAS FOR $2;
    v_current DATE DEFAULT v_from;
BEGIN
    WHILE (v_current<=v_to) LOOP
        RETURN NEXT v_current;
        v_current:=v_current+1;
    END LOOP;
   
    RETURN;
END;
';

test it:
SELECT * FROM calendar('2005-01-01', '2005-01-31');


Muhyiddin A.M Hayat wrote:

> Dear All,
>  
> How to create Calendar Function or Query. I would like to display date 
> form yyyy-mm-dd to yyyy-mm-dd or display date in one Month
>  
> e.g:
>  
>     date
> ------------
>  2005-01-01
>  2005-01-02
>  2005-01-03
>  2005-01-04
>  2005-01-05
>  2005-01-06
>  2005-01-07
>  2005-01-08
>  2005-01-09
>  2005-01-10
>  2005-01-11
>  2005-01-12
>  2005-01-13
>  2005-01-14
>  2005-01-15
>  2005-01-16
>  2005-01-17
>  2005-01-18
>  2005-01-19
>  2005-01-20
>  2005-01-21
>  2005-01-22
>  2005-01-23
>  2005-01-24
>  2005-01-25
>  2005-01-26
>  2005-01-27
>  2005-01-28
>  2005-01-29
>  2005-01-30
>  2005-01-31
>  
>  



--------------000502020909030806010808
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
  <title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
maybe somthing like this:<br>
<br>
CREATE OR REPLACE FUNCTION calendar (DATE, DATE) RETURNS SETOF DATE
LANGUAGE 'plpgsql' AS '<br>
DECLARE<br>
&nbsp;&nbsp;&nbsp; v_from ALIAS FOR $1;<br>
&nbsp;&nbsp;&nbsp; v_to ALIAS FOR $2;<br>
&nbsp;&nbsp;&nbsp; v_current DATE DEFAULT v_from;<br>
BEGIN<br>
&nbsp;&nbsp;&nbsp; WHILE (v_current&lt;=v_to) LOOP <br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; RETURN NEXT v_current;<br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; v_current:=v_current+1;<br>
&nbsp;&nbsp;&nbsp; END LOOP;<br>
&nbsp;&nbsp;&nbsp; <br>
&nbsp;&nbsp;&nbsp; RETURN;<br>
END;<br>
';<br>
<br>
test it:<br>
SELECT * FROM calendar('2005-01-01', '2005-01-31');<br>
<br>
<br>
Muhyiddin A.M Hayat wrote:
<blockquote cite="mid03a001c50545$6bd942d0$1f00a8c0@middinks"
 type="cite">
  <meta http-equiv="Content-Type" content="text/html; ">
  <meta content="MSHTML 6.00.2900.2523" name="GENERATOR">
  <style></style>
  <div><font face="Arial" size="2">Dear All,</font></div>
  <div>&nbsp;</div>
  <div><font face="Arial" size="2">How to create Calendar Function or
Query. I would like to display date form yyyy-mm-dd to yyyy-mm-dd or
display date in one Month</font></div>
  <div>&nbsp;</div>
  <div><font face="Arial" size="2">e.g:</font></div>
  <div>&nbsp;</div>
  <div><font face="Arial" size="2">&nbsp;&nbsp;&nbsp; date<br>
------------<br>
&nbsp;2005-01-01<br>
&nbsp;2005-01-02<br>
&nbsp;2005-01-03<br>
&nbsp;2005-01-04<br>
&nbsp;2005-01-05<br>
&nbsp;2005-01-06<br>
&nbsp;2005-01-07<br>
&nbsp;2005-01-08<br>
&nbsp;2005-01-09<br>
&nbsp;2005-01-10<br>
&nbsp;2005-01-11<br>
&nbsp;2005-01-12<br>
&nbsp;2005-01-13<br>
&nbsp;2005-01-14<br>
&nbsp;2005-01-15<br>
&nbsp;2005-01-16<br>
&nbsp;2005-01-17<br>
&nbsp;2005-01-18<br>
&nbsp;2005-01-19<br>
&nbsp;2005-01-20<br>
&nbsp;2005-01-21<br>
&nbsp;2005-01-22<br>
&nbsp;2005-01-23<br>
&nbsp;2005-01-24<br>
&nbsp;2005-01-25<br>
&nbsp;2005-01-26<br>
&nbsp;2005-01-27<br>
&nbsp;2005-01-28<br>
&nbsp;2005-01-29<br>
&nbsp;2005-01-30<br>
&nbsp;2005-01-31</font></div>
  <div>&nbsp;</div>
  <div>&nbsp;</div>
</blockquote>
<br>
</body>
</html>

--------------000502020909030806010808--



From middink@indo.net.id Wed Feb  2 02:53:33 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 3F6638B9BBB
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed,  2 Feb 2005 02:53:33 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 40860-04
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed,  2 Feb 2005 02:53:25 +0000 (GMT)
Received: from smtp-1.indo.net.id (smtp-1.indo.net.id [202.159.32.91])
	by svr1.postgresql.org (Postfix) with SMTP id 624438B9B5B
	for <pgsql-sql@postgresql.org>; Wed,  2 Feb 2005 02:53:21 +0000 (GMT)
Received: (qmail 14468 invoked from network); 2 Feb 2005 09:53:18 +0700
Received: from smtp.indo.net.id (202.159.32.81)
	by smtp-1.indo.net.id with SMTP; 2 Feb 2005 09:53:18 +0700
Received: (qmail 3765 invoked from network); 2 Feb 2005 09:53:15 +0700
Received: from unknown (HELO middinks) (202.159.122.97)
	by smtp.indo.net.id with SMTP; 2 Feb 2005 09:53:15 +0700
Message-ID: <018901c508d2$55bc3800$1f00a8c0@middinks>
From: "Muhyiddin A.M Hayat" <middink@indo.net.id>
To: "Franco Bruno Borghesi" <franco@akyasociados.com.ar>
Cc: <pgsql-sql@postgresql.org>
References: <03a001c50545$6bd942d0$1f00a8c0@middinks>
	<41FA5E6A.50002@akyasociados.com.ar>
Subject: Re: Calendar Function
Date: Wed, 2 Feb 2005 10:53:09 +0800
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_0186_01C50915.621391A0"
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2900.2180
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.44 tagged_above=0 required=5 tests=AWL,
	DNS_FROM_RFC_WHOIS, HTML_60_70, HTML_MESSAGE, HTML_TITLE_EMPTY
X-Spam-Level: 
X-Archive-Number: 200502/37
X-Sequence-Number: 20148

This is a multi-part message in MIME format.

------=_NextPart_000_0186_01C50915.621391A0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Ok, thanks

But if i would like to display date in one Month,

 e.g :
date in feb 2005

  calendar
------------
 2005-02-01
 2005-02-02
 2005-02-03
 2005-02-04
 2005-02-05
 2005-02-06
 2005-02-07
 2005-02-08
 2005-02-09
 2005-02-10
 2005-02-11
 2005-02-12
 2005-02-13
 2005-02-14
 2005-02-15
 2005-02-16
 2005-02-17
 2005-02-18
 2005-02-19
 2005-02-20
 2005-02-21
 2005-02-22
 2005-02-23
 2005-02-24
 2005-02-25
 2005-02-26
 2005-02-27
 2005-02-28

date in feb 2004

  calendar
------------
 2004-02-01
 2004-02-02
 2004-02-03
 2004-02-04
 2004-02-05
 2004-02-06
 2004-02-07
 2004-02-08
 2004-02-09
 2004-02-10
 2004-02-11
 2004-02-12
 2004-02-13
 2004-02-14
 2004-02-15
 2004-02-16
 2004-02-17
 2004-02-18
 2004-02-19
 2004-02-20
 2004-02-21
 2004-02-22
 2004-02-23
 2004-02-24
 2004-02-25
 2004-02-26
 2004-02-27
 2004-02-28
 2004-02-29

  ----- Original Message -----=20
  From: Franco Bruno Borghesi=20
  To: Muhyiddin A.M Hayat=20
  Cc: pgsql-sql@postgresql.org=20
  Sent: Friday, January 28, 2005 11:46 PM
  Subject: Re: [SQL] Calendar Function


  maybe somthing like this:

  CREATE OR REPLACE FUNCTION calendar (DATE, DATE) RETURNS SETOF DATE =
LANGUAGE 'plpgsql' AS '
  DECLARE
      v_from ALIAS FOR $1;
      v_to ALIAS FOR $2;
      v_current DATE DEFAULT v_from;
  BEGIN
      WHILE (v_current<=3Dv_to) LOOP=20
          RETURN NEXT v_current;
          v_current:=3Dv_current+1;
      END LOOP;
     =20
      RETURN;
  END;
  ';

  test it:
  SELECT * FROM calendar('2005-01-01', '2005-01-31');


  Muhyiddin A.M Hayat wrote:=20
    Dear All,

    How to create Calendar Function or Query. I would like to display =
date form yyyy-mm-dd to yyyy-mm-dd or display date in one Month

    e.g:

        date
    ------------
     2005-01-01
     2005-01-02
     2005-01-03
     2005-01-04
     2005-01-05
     2005-01-06
     2005-01-07
     2005-01-08
     2005-01-09
     2005-01-10
     2005-01-11
     2005-01-12
     2005-01-13
     2005-01-14
     2005-01-15
     2005-01-16
     2005-01-17
     2005-01-18
     2005-01-19
     2005-01-20
     2005-01-21
     2005-01-22
     2005-01-23
     2005-01-24
     2005-01-25
     2005-01-26
     2005-01-27
     2005-01-28
     2005-01-29
     2005-01-30
     2005-01-31




------=_NextPart_000_0186_01C50915.621391A0
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD><TITLE></TITLE>
<META http-equiv=3DContent-Type =
content=3Dtext/html;charset=3DISO-8859-1>
<META content=3D"MSHTML 6.00.2900.2523" name=3DGENERATOR></HEAD>
<BODY text=3D#000000 bgColor=3D#ffffff>
<DIV>
<DIV>Ok, thanks</DIV>
<DIV>&nbsp;</DIV>
<DIV>But if i would like to display&nbsp;date in one <FONT face=3DArial=20
size=3D2>Month,</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>&nbsp;e.g :</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>date in&nbsp;feb 2005</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>&nbsp;=20
calendar<BR>------------<BR>&nbsp;2005-02-01<BR>&nbsp;2005-02-02<BR>&nbsp=
;2005-02-03<BR>&nbsp;2005-02-04<BR>&nbsp;2005-02-05<BR>&nbsp;2005-02-06<B=
R>&nbsp;2005-02-07<BR>&nbsp;2005-02-08<BR>&nbsp;2005-02-09<BR>&nbsp;2005-=
02-10<BR>&nbsp;2005-02-11<BR>&nbsp;2005-02-12<BR>&nbsp;2005-02-13<BR>&nbs=
p;2005-02-14<BR>&nbsp;2005-02-15<BR>&nbsp;2005-02-16<BR>&nbsp;2005-02-17<=
BR>&nbsp;2005-02-18<BR>&nbsp;2005-02-19<BR>&nbsp;2005-02-20<BR>&nbsp;2005=
-02-21<BR>&nbsp;2005-02-22<BR>&nbsp;2005-02-23<BR>&nbsp;2005-02-24<BR>&nb=
sp;2005-02-25<BR>&nbsp;2005-02-26<BR>&nbsp;2005-02-27<BR>&nbsp;2005-02-28=
</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV>
<DIV><FONT face=3DArial size=3D2>date in&nbsp;feb 2004</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV>&nbsp;=20
calendar<BR>------------<BR>&nbsp;2004-02-01<BR>&nbsp;2004-02-02<BR>&nbsp=
;2004-02-03<BR>&nbsp;2004-02-04<BR>&nbsp;2004-02-05<BR>&nbsp;2004-02-06<B=
R>&nbsp;2004-02-07<BR>&nbsp;2004-02-08<BR>&nbsp;2004-02-09<BR>&nbsp;2004-=
02-10<BR>&nbsp;2004-02-11<BR>&nbsp;2004-02-12<BR>&nbsp;2004-02-13<BR>&nbs=
p;2004-02-14<BR>&nbsp;2004-02-15<BR>&nbsp;2004-02-16<BR>&nbsp;2004-02-17<=
BR>&nbsp;2004-02-18<BR>&nbsp;2004-02-19<BR>&nbsp;2004-02-20<BR>&nbsp;2004=
-02-21<BR>&nbsp;2004-02-22<BR>&nbsp;2004-02-23<BR>&nbsp;2004-02-24<BR>&nb=
sp;2004-02-25<BR>&nbsp;2004-02-26<BR>&nbsp;2004-02-27<BR>&nbsp;2004-02-28=
<BR>&nbsp;2004-02-29</DIV></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV></DIV>
<BLOCKQUOTE=20
style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =
BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
  <DIV style=3D"FONT: 10pt arial">----- Original Message ----- </DIV>
  <DIV=20
  style=3D"BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: =
black"><B>From:</B>=20
  <A title=3Dfranco@akyasociados.com.ar=20
  href=3D"mailto:franco@akyasociados.com.ar">Franco Bruno Borghesi</A> =
</DIV>
  <DIV style=3D"FONT: 10pt arial"><B>To:</B> <A =
title=3Dmiddink@indo.net.id=20
  href=3D"mailto:middink@indo.net.id">Muhyiddin A.M Hayat</A> </DIV>
  <DIV style=3D"FONT: 10pt arial"><B>Cc:</B> <A =
title=3Dpgsql-sql@postgresql.org=20
  href=3D"mailto:pgsql-sql@postgresql.org">pgsql-sql@postgresql.org</A> =
</DIV>
  <DIV style=3D"FONT: 10pt arial"><B>Sent:</B> Friday, January 28, 2005 =
11:46=20
  PM</DIV>
  <DIV style=3D"FONT: 10pt arial"><B>Subject:</B> Re: [SQL] Calendar=20
Function</DIV>
  <DIV><BR></DIV>maybe somthing like this:<BR><BR>CREATE OR REPLACE =
FUNCTION=20
  calendar (DATE, DATE) RETURNS SETOF DATE LANGUAGE 'plpgsql' AS=20
  '<BR>DECLARE<BR>&nbsp;&nbsp;&nbsp; v_from ALIAS FOR =
$1;<BR>&nbsp;&nbsp;&nbsp;=20
  v_to ALIAS FOR $2;<BR>&nbsp;&nbsp;&nbsp; v_current DATE DEFAULT=20
  v_from;<BR>BEGIN<BR>&nbsp;&nbsp;&nbsp; WHILE (v_current&lt;=3Dv_to) =
LOOP=20
  <BR>&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; RETURN NEXT=20
  v_current;<BR>&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;=20
  v_current:=3Dv_current+1;<BR>&nbsp;&nbsp;&nbsp; END =
LOOP;<BR>&nbsp;&nbsp;&nbsp;=20
  <BR>&nbsp;&nbsp;&nbsp; RETURN;<BR>END;<BR>';<BR><BR>test it:<BR>SELECT =
* FROM=20
  calendar('2005-01-01', '2005-01-31');<BR><BR><BR>Muhyiddin A.M Hayat =
wrote:=20
  <BLOCKQUOTE cite=3Dmid03a001c50545$6bd942d0$1f00a8c0@middinks =
type=3D"cite">
    <META content=3D"MSHTML 6.00.2900.2523" name=3DGENERATOR>
    <STYLE></STYLE>

    <DIV><FONT face=3DArial size=3D2>Dear All,</FONT></DIV>
    <DIV>&nbsp;</DIV>
    <DIV><FONT face=3DArial size=3D2>How to create Calendar Function or =
Query. I=20
    would like to display date form yyyy-mm-dd to yyyy-mm-dd or display =
date in=20
    one Month</FONT></DIV>
    <DIV>&nbsp;</DIV>
    <DIV><FONT face=3DArial size=3D2>e.g:</FONT></DIV>
    <DIV>&nbsp;</DIV>
    <DIV><FONT face=3DArial size=3D2>&nbsp;&nbsp;&nbsp;=20
    =
date<BR>------------<BR>&nbsp;2005-01-01<BR>&nbsp;2005-01-02<BR>&nbsp;200=
5-01-03<BR>&nbsp;2005-01-04<BR>&nbsp;2005-01-05<BR>&nbsp;2005-01-06<BR>&n=
bsp;2005-01-07<BR>&nbsp;2005-01-08<BR>&nbsp;2005-01-09<BR>&nbsp;2005-01-1=
0<BR>&nbsp;2005-01-11<BR>&nbsp;2005-01-12<BR>&nbsp;2005-01-13<BR>&nbsp;20=
05-01-14<BR>&nbsp;2005-01-15<BR>&nbsp;2005-01-16<BR>&nbsp;2005-01-17<BR>&=
nbsp;2005-01-18<BR>&nbsp;2005-01-19<BR>&nbsp;2005-01-20<BR>&nbsp;2005-01-=
21<BR>&nbsp;2005-01-22<BR>&nbsp;2005-01-23<BR>&nbsp;2005-01-24<BR>&nbsp;2=
005-01-25<BR>&nbsp;2005-01-26<BR>&nbsp;2005-01-27<BR>&nbsp;2005-01-28<BR>=
&nbsp;2005-01-29<BR>&nbsp;2005-01-30<BR>&nbsp;2005-01-31</FONT></DIV>
    <DIV>&nbsp;</DIV>
    <DIV>&nbsp;</DIV></BLOCKQUOTE><BR></BLOCKQUOTE></BODY></HTML>

------=_NextPart_000_0186_01C50915.621391A0--





From bmiller@nuvio.com Wed Feb  2 03:04:44 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id E5D448B9BDF
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed,  2 Feb 2005 03:04:44 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 42983-07
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed,  2 Feb 2005 03:04:31 +0000 (GMT)
Received: from lothos.nuvio.com (lothos.ojc.nuvio.com [63.77.68.101])
	by svr1.postgresql.org (Postfix) with ESMTP id C32208B9D0D
	for <pgsql-sql@postgresql.org>; Wed,  2 Feb 2005 03:04:33 +0000 (GMT)
Received: from [63.77.68.30] ([63.77.68.30]) by lothos.nuvio.com with
	Microsoft SMTPSVC(5.0.2195.6713); Tue, 1 Feb 2005 21:06:28 -0600
Mime-Version: 1.0 (Apple Message framework v619)
In-Reply-To: <018901c508d2$55bc3800$1f00a8c0@middinks>
References: <03a001c50545$6bd942d0$1f00a8c0@middinks>
	<41FA5E6A.50002@akyasociados.com.ar>
	<018901c508d2$55bc3800$1f00a8c0@middinks>
Content-Type: multipart/alternative; boundary=Apple-Mail-7-157219216
Message-Id: <14CBF7DC-74C7-11D9-AC7D-000D932ED682@nuvio.com>
From: Bradley Miller <bmiller@nuvio.com>
Subject: Re: Calendar Function
Date: Tue, 1 Feb 2005 21:03:58 -0600
To: <pgsql-sql@postgresql.org> <pgsql-sql@postgresql.org>
	<pgsql-sql@postgresql.org>
X-Mailer: Apple Mail (2.619)
X-OriginalArrivalTime: 02 Feb 2005 03:06:28.0328 (UTC)
	FILETIME=[301DDE80:01C508D4]
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.262 tagged_above=0 required=5 tests=AWL
X-Spam-Level: 
X-Archive-Number: 200502/38
X-Sequence-Number: 20149


--Apple-Mail-7-157219216
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
	charset=ISO-8859-1;
	format=flowed

You might need to get creative and do some functionality in another=20
language, like C or PHP via the PL integration.  (I know I just saw=20
something for PHP  . . . the question is can you use PHP functions ?  =20=

? )


On Feb 1, 2005, at 8:53 PM, Muhyiddin A.M Hayat wrote:

> Ok, thanks
> =A0
> But if i would like to display=A0date in one Month,
> =A0
> =A0e.g :
> date in=A0feb 2005
> =A0
> =A0 calendar
> ------------
> =A02005-02-01
> =A02005-02-02
> =A02005-02-03
> =A02005-02-04
> =A02005-02-05
> ** SNIP **
Bradley Miller
NUVIO CORPORATION
Phone: 816-444-4422 ext. 6757
Fax: 913-498-1810
http://www.nuvio.com
bmiller@nuvio.com

--Apple-Mail-7-157219216
Content-Transfer-Encoding: quoted-printable
Content-Type: text/enriched;
	charset=ISO-8859-1

You might need to get creative and do some functionality in another
language, like C or PHP via the PL integration.  (I know I just saw
something for PHP  . . . the question is can you use PHP functions ? =20
? )



On Feb 1, 2005, at 8:53 PM, Muhyiddin A.M Hayat wrote:


<excerpt>Ok, thanks

=A0

But if i would like to display=A0date in one
<fontfamily><param>Arial</param><smaller>Month,</smaller></fontfamily>

=A0

<fontfamily><param>Arial</param><smaller>=A0e.g :</smaller></fontfamily>

<fontfamily><param>Arial</param><smaller>date in=A0feb =
2005</smaller></fontfamily>

=A0

<fontfamily><param>Arial</param><smaller>=A0 =
calendar</smaller></fontfamily>

=
<fontfamily><param>Arial</param><smaller>------------</smaller></fontfamil=
y>

=
<fontfamily><param>Arial</param><smaller>=A02005-02-01</smaller></fontfami=
ly>

=
<fontfamily><param>Arial</param><smaller>=A02005-02-02</smaller></fontfami=
ly>

=
<fontfamily><param>Arial</param><smaller>=A02005-02-03</smaller></fontfami=
ly>

=
<fontfamily><param>Arial</param><smaller>=A02005-02-04</smaller></fontfami=
ly>

=
<fontfamily><param>Arial</param><smaller>=A02005-02-05</smaller></fontfami=
ly>

** SNIP **

</excerpt>Bradley Miller

=
<bold><fontfamily><param>Arial</param><color><param>1313,0000,FFFF</param>=
<x-tad-bigger>NUVIO
=
CORPORATION</x-tad-bigger></color></fontfamily></bold><fontfamily><param>A=
rial</param><x-tad-bigger>

Phone: 816-444-4422 ext. 6757

Fax: 913-498-1810

=
</x-tad-bigger><color><param>0000,0000,FFFF</param><x-tad-bigger>http://ww=
w.nuvio.com</x-tad-bigger></color><x-tad-bigger>

=
</x-tad-bigger><color><param>0000,0000,FFFF</param><x-tad-bigger>bmiller@n=
uvio.com</x-tad-bigger></color></fontfamily>


--Apple-Mail-7-157219216--




From mike@fuhr.org Wed Feb  2 03:46:34 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 6F97A8B9C6C
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed,  2 Feb 2005 03:46:34 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 46633-09
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed,  2 Feb 2005 03:46:27 +0000 (GMT)
Received: from tigger.fuhr.org (tigger.fuhr.org [63.214.45.158])
	by svr1.postgresql.org (Postfix) with ESMTP id 3B9B48B9C1E
	for <pgsql-sql@postgresql.org>; Wed,  2 Feb 2005 03:46:29 +0000 (GMT)
Received: from winnie.fuhr.org (winnie.fuhr.org [10.1.0.1])
	by tigger.fuhr.org (8.13.1/8.13.1) with ESMTP id j123jaQI009049
	(version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=OK);
	Tue, 1 Feb 2005 20:45:39 -0700 (MST)
	(envelope-from mfuhr@winnie.fuhr.org)
Received: from winnie.fuhr.org (localhost [127.0.0.1])
	by winnie.fuhr.org (8.13.1/8.13.1) with ESMTP id j123javC009553;
	Tue, 1 Feb 2005 20:45:36 -0700 (MST)
	(envelope-from mfuhr@winnie.fuhr.org)
Received: (from mfuhr@localhost)
	by winnie.fuhr.org (8.13.1/8.13.1/Submit) id j123jZMp009494;
	Tue, 1 Feb 2005 20:45:35 -0700 (MST) (envelope-from mfuhr)
Date: Tue, 1 Feb 2005 20:45:35 -0700
From: Michael Fuhr <mike@fuhr.org>
To: "Muhyiddin A.M Hayat" <middink@indo.net.id>
Cc: Franco Bruno Borghesi <franco@akyasociados.com.ar>,
	pgsql-sql@postgresql.org
Subject: Re: Calendar Function
Message-ID: <20050202034535.GA98781@winnie.fuhr.org>
References: <03a001c50545$6bd942d0$1f00a8c0@middinks>
	<41FA5E6A.50002@akyasociados.com.ar>
	<018901c508d2$55bc3800$1f00a8c0@middinks>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <018901c508d2$55bc3800$1f00a8c0@middinks>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.003 tagged_above=0 required=5 tests=AWL
X-Spam-Level: 
X-Archive-Number: 200502/39
X-Sequence-Number: 20150

On Wed, Feb 02, 2005 at 10:53:09AM +0800, Muhyiddin A.M Hayat wrote:
> 
> But if i would like to display date in one Month,

You could use the given function with a few changes.  For example,
given an arbitrary date, you could use date_trunc() to find the
first day of that date's month, add an interval of 1 month to find
the first day of the following month, and use a loop to return the
dates up to but not including the latter value.

For more information, see the "Date/Time Functions and Operators"
section in the "Functions and Operators" chapter of the documentation:

http://www.postgresql.org/docs/8.0/static/functions-datetime.html

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/



From jboes@nexcerpt.com Thu Feb  3 19:56:24 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 913BC8B9F32
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu,  3 Feb 2005 19:56:24 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 82680-06
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu,  3 Feb 2005 19:56:20 +0000 (GMT)
Received: from floppy.pyrenet.fr (news.pyrenet.fr [194.250.190.2])
	by svr1.postgresql.org (Postfix) with ESMTP id C78C88B9E7F
	for <pgsql-sql@postgresql.org>; Thu,  3 Feb 2005 19:56:20 +0000 (GMT)
Received: by floppy.pyrenet.fr (Postfix, from userid 106)
	id 6167C31DD1; Thu,  3 Feb 2005 20:56:20 +0100 (MET)
From: Jeff Boes <jboes@nexcerpt.com>
X-Newsgroups: pgsql.sql
Subject: Re: Calendar Function
Date: Thu, 03 Feb 2005 14:56:14 -0500
Organization: Hub.Org Networking Services
Lines: 40
Message-ID: <cttvkv$114u$1@news.hub.org>
References: <03a001c50545$6bd942d0$1f00a8c0@middinks>
	<41FA5E6A.50002@akyasociados.com.ar>
	<018901c508d2$55bc3800$1f00a8c0@middinks>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Complaints-To: usenet@news.hub.org
User-Agent: Mozilla Thunderbird 1.0 (X11/20041206)
X-Accept-Language: en-us, en
In-Reply-To: <018901c508d2$55bc3800$1f00a8c0@middinks>
To: pgsql-sql@postgresql.org
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=
X-Spam-Level: 
X-Archive-Number: 200502/76
X-Sequence-Number: 20187

Muhyiddin A.M Hayat wrote:
> Ok, thanks
>  
> But if i would like to display date in one Month,
>  
>  e.g :
> date in feb 2005

You can do that in Pg date arithmetic:

# select '1 oct 2004'::date + '1 month'::interval - '1 day'::interval;
       ?column?
---------------------
  2004-10-31 00:00:00
(1 row)

# select '1 nov 2004'::date + '1 month'::interval - '1 day'::interval;
       ?column?
---------------------
  2004-11-30 00:00:00
(1 row)

# select '1 feb 2004'::date + '1 month'::interval - '1 day'::interval;
       ?column?
---------------------
  2004-02-29 00:00:00
(1 row)


Thus, given the original response to your question:

select * from calendar('1 feb 2004', ( '1 feb 2004'::date +
'1 month'::interval - '1 day'::interval )::date);


-- 
Jeff Boes                                      vox 269.226.9550 ext 24
http://www.nexcerpt.com                        fax 269.349.9076

            ...Nexcerpt... Extend your Expertise