Calendar Function

[Muhyiddin A.M Hayat]

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

 

 

[Franco Bruno Borghesi]

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]

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

 

[Jeff Boes]

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

[Michael Fuhr]

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/

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

[Adam Witney]

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
> bmi...@nuvio.com

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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Bradley Miller]

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 ?
? )

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

> 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

> ** SNIP **

[Kretschmer Andreas]

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° ;-)

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majo...@postgresql.org

[Bradley Miller]

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

[Michael Fuhr]

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

[Bradley Miller]

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