pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedCalendar Function
6+ messages / 5 participants
[nested] [flat]
* Calendar Function
@ 2005-01-28 14:26 Muhyiddin A.M Hayat <middink@indo.net.id>
2005-01-28 15:46 ` Re: Calendar Function Franco Bruno Borghesi <franco@akyasociados.com.ar>
0 siblings, 1 reply; 6+ messages in thread
From: Muhyiddin A.M Hayat @ 2005-01-28 14:26 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Calendar Function
2005-01-28 14:26 Calendar Function Muhyiddin A.M Hayat <middink@indo.net.id>
@ 2005-01-28 15:46 ` Franco Bruno Borghesi <franco@akyasociados.com.ar>
2005-02-02 02:53 ` Re: Calendar Function Muhyiddin A.M Hayat <middink@indo.net.id>
0 siblings, 1 reply; 6+ messages in thread
From: Franco Bruno Borghesi @ 2005-01-28 15:46 UTC (permalink / raw)
To: Muhyiddin A.M Hayat <middink@indo.net.id>; +Cc: pgsql-sql
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
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Calendar Function
2005-01-28 14:26 Calendar Function Muhyiddin A.M Hayat <middink@indo.net.id>
2005-01-28 15:46 ` Re: Calendar Function Franco Bruno Borghesi <franco@akyasociados.com.ar>
@ 2005-02-02 02:53 ` Muhyiddin A.M Hayat <middink@indo.net.id>
2005-02-02 03:03 ` Re: Calendar Function Bradley Miller <bmiller@nuvio.com>
2005-02-02 03:45 ` Re: Calendar Function Michael Fuhr <mike@fuhr.org>
2005-02-03 19:56 ` Re: Calendar Function Jeff Boes <jboes@nexcerpt.com>
0 siblings, 3 replies; 6+ messages in thread
From: Muhyiddin A.M Hayat @ 2005-02-02 02:53 UTC (permalink / raw)
To: Franco Bruno Borghesi <franco@akyasociados.com.ar>; +Cc: pgsql-sql
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 -----
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:
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Calendar Function
2005-01-28 14:26 Calendar Function Muhyiddin A.M Hayat <middink@indo.net.id>
2005-01-28 15:46 ` Re: Calendar Function Franco Bruno Borghesi <franco@akyasociados.com.ar>
2005-02-02 02:53 ` Re: Calendar Function Muhyiddin A.M Hayat <middink@indo.net.id>
@ 2005-02-02 03:03 ` Bradley Miller <bmiller@nuvio.com>
2 siblings, 0 replies; 6+ messages in thread
From: Bradley Miller @ 2005-02-02 03:03 UTC (permalink / raw)
To: pgsql-sql; pgsql-sql; pgsql-sql
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 **
Bradley Miller
NUVIO CORPORATION
Phone: 816-444-4422 ext. 6757
Fax: 913-498-1810
http://www.nuvio.com
bmiller@nuvio.com
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Calendar Function
2005-01-28 14:26 Calendar Function Muhyiddin A.M Hayat <middink@indo.net.id>
2005-01-28 15:46 ` Re: Calendar Function Franco Bruno Borghesi <franco@akyasociados.com.ar>
2005-02-02 02:53 ` Re: Calendar Function Muhyiddin A.M Hayat <middink@indo.net.id>
@ 2005-02-02 03:45 ` Michael Fuhr <mike@fuhr.org>
2 siblings, 0 replies; 6+ messages in thread
From: Michael Fuhr @ 2005-02-02 03:45 UTC (permalink / raw)
To: Muhyiddin A.M Hayat <middink@indo.net.id>; +Cc: Franco Bruno Borghesi <franco@akyasociados.com.ar>; pgsql-sql
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/
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Calendar Function
2005-01-28 14:26 Calendar Function Muhyiddin A.M Hayat <middink@indo.net.id>
2005-01-28 15:46 ` Re: Calendar Function Franco Bruno Borghesi <franco@akyasociados.com.ar>
2005-02-02 02:53 ` Re: Calendar Function Muhyiddin A.M Hayat <middink@indo.net.id>
@ 2005-02-03 19:56 ` Jeff Boes <jboes@nexcerpt.com>
2 siblings, 0 replies; 6+ messages in thread
From: Jeff Boes @ 2005-02-03 19:56 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2005-02-03 19:56 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-01-28 14:26 Calendar Function Muhyiddin A.M Hayat <middink@indo.net.id>
2005-01-28 15:46 ` Franco Bruno Borghesi <franco@akyasociados.com.ar>
2005-02-02 02:53 ` Muhyiddin A.M Hayat <middink@indo.net.id>
2005-02-02 03:03 ` Bradley Miller <bmiller@nuvio.com>
2005-02-02 03:45 ` Michael Fuhr <mike@fuhr.org>
2005-02-03 19:56 ` Jeff Boes <jboes@nexcerpt.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox