Thread: 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<=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

 

 

----- Original Message -----

From: Franco Bruno Borghesi

To: Muhyiddin A.M Hayat

Cc: pgsql-sql@postgresql.org

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<=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:

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=20=
=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

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/

Is there a way to dump everything in a particular schema?


Bradley Miller
NUVIO CORPORATION
Phone: 816-444-4422 ext. 6757
Fax: 913-498-1810
http://www.nuvio.com
bmiller@nuvio.com

am  Wed, dem 02.02.2005, um  9:33:22 -0600 mailte Bradley Miller folgendes:
> Is there a way to dump everything in a particular schema?

RTFM.

man pg_dump, search for --schema


Regards, Andreas
-- 
Diese Message wurde erstellt mit freundlicher Unterstützung eines freilau-
fenden Pinguins aus artgerechter Freilandhaltung.   Er ist garantiert frei
von Micro$oft'schen Viren. (#97922 http://counter.li.org)     GPG 7F4584DA
Was, Sie wissen nicht, wo Kaufbach ist? Hier: N 51.05082°, E 13.56889° ;-)

From: pg_dump --help

-n, --schema=SCHEMA      dump the named schema only



> Is there a way to dump everything in a particular schema?
> 
> 
> Bradley Miller
> NUVIO CORPORATION
> Phone: 816-444-4422 ext. 6757
> Fax: 913-498-1810
> http://www.nuvio.com
> bmiller@nuvio.com


-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

On Wed, Feb 02, 2005 at 09:33:22AM -0600, Bradley Miller wrote:

> Is there a way to dump everything in a particular schema? 

See the documentation for pg_dump.  In PostgreSQL 7.4 and later,
pg_dump has a --schema (-n) option.

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

Thanks -- I was looking at a book and it didn't mention the schema dump
. . . should have done the man or --help before asking.


On Feb 2, 2005, at 9:46 AM, Adam Witney wrote:

>
> From: pg_dump --help
>
> -n, --schema=SCHEMA      dump the named schema only
>

Bradley Miller
NUVIO CORPORATION
Phone: 816-444-4422 ext. 6757
Fax: 913-498-1810
http://www.nuvio.com
bmiller@nuvio.com

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