pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedReturning a set of dates
3+ messages / 2 participants
[nested] [flat]
* Returning a set of dates
@ 2011-06-11 04:28 C. Bensend <benny@bennyvision.com>
0 siblings, 1 reply; 3+ messages in thread
From: C. Bensend @ 2011-06-11 04:28 UTC (permalink / raw)
To: pgsql-sql
Hey folks,
I am still slogging away on my pet project, and I'm giving up - I
need help from the experts to try to get this function working like
I want it to.
I have a very basic function, thanks to you guys a few months ago:
CREATE OR REPLACE FUNCTION public.next_bill_date(d date, period interval,
i interval)
RETURNS date
LANGUAGE plpgsql
AS $function$ DECLARE ret DATE; BEGIN
-- We must use "CURRENT_DATE + period" in SELECT below - if we just
-- use "d + i", we will only get the next billing date after the
-- bill's *epoch*. Since we're passing an epoch, we need to
-- make sure we're getting the real next billing date
SELECT INTO ret generate_series( d, CURRENT_DATE + period, i ) AS
next_bill_date
ORDER BY next_bill_date DESC; RETURN ret;
END;
$function$
If I call the function like so:
SELECT next_bill_date( '2011-06-10', '1 month', '1 year' );
next_bill_date
----------------
2011-06-10
.. it gives me the *next date* a bill will be due. Yay. However,
several of my bills are recurring in a time period shorter than the
interval I will pass the function. Ie, I have several savings "bills"
that occur twice a month, aligned with my paycheck. If I call the
function:
SELECT next_bill_date( '2011-06-01', '2 weeks', '1 month' )
.. I need it to return the two dates during the interval (1 month)
that this "bill" will be due. I am brain-weary looking at this, so
in case my explanation is not clear, here's what I'm shooting for:
1) The function must accept an epoch date, when the bill "starts". Ie,
my mortgage's epoch is on 2011-01-01, as it's due on the 1st of
the month. The month and year aren't as critical, they just need
to represent a "starting date" in the past. This epoch could just
as well be '2011-06-01'. I manually enter the epochs, so I can make
the assumption that it will always be in the past.
2) The function must accept a period, or how often the bill recurs.
So, most bills will have a period of '1 month'. Some might be
'2 weeks'. Some, like insurance, might be '6 months' or even
'1 year'.
3) The function must accept an interval, describing how long of a
time period we want to look at. Ie, "I want to look at all bills
over the next six months." The interval would be '6 months'.
Or the upcoming bills over '6 weeks'. You get the idea.
So, for example, if I call the function to determine my mortgage's
due dates over the next four months:
SELECT next_bill_date( '2011-01-01', '1 month', '4 months' );
.. I expect the following result set:
next_bill_date
----------------
2011-07-01
2011-08-01
2011-09-01
2011-10-01
I know I must use SETOF to return the set. But I just can't seem to
get the damned syntax correct, and I'm more than a little lost trying
to get this function put together. Can someone please help me out?
Thanks much!
Benny
--
"You were doing well until everyone died."
-- "God", Futurama
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Returning a set of dates
@ 2011-06-11 05:11 Samuel Gendler <sgendler@ideasculptor.com>
parent: C. Bensend <benny@bennyvision.com>
0 siblings, 0 replies; 3+ messages in thread
From: Samuel Gendler @ 2011-06-11 05:11 UTC (permalink / raw)
To: C. Bensend <benny@bennyvision.com>; +Cc: pgsql-sql
On Fri, Jun 10, 2011 at 9:28 PM, C. Bensend <benny@bennyvision.com> wrote:
> SELECT next_bill_date( '2011-01-01', '1 month', '4 months' );
>
>
> .. I expect the following result set:
>
>
> next_bill_date
> ----------------
>
> 2011-07-01
> 2011-08-01
> 2011-09-01
> 2011-10-01
>
>
>
http://www.postgresql.org/docs/8.4/interactive/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RE...
CREATE OR REPLACE FUNCTION public.next_bill_date(d date, period interval,
i interval)
RETURNS SETOF date
AS $function$
DECLARE
max_date date;
due_date date;
BEGIN
max_date := CURRENT_DATE + i;
due_date := d;
WHILE due_date + period <= max_date LOOP
RETURN NEXT due_date; -- add d to the result set
due_date := due_date + period;
END LOOP;
RETURN; -- exit function
END;
$function$ language plpgsql;
testdb=# select next_bill_date('2011-06-11', '2 week', '3 month');
next_bill_date
----------------
2011-06-11
2011-06-25
2011-07-09
2011-07-23
2011-08-06
2011-08-20
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Returning a set of dates
@ 2011-06-11 22:18 C. Bensend <benny@bennyvision.com>
0 siblings, 0 replies; 3+ messages in thread
From: C. Bensend @ 2011-06-11 22:18 UTC (permalink / raw)
To: pgsql-sql
> http://www.postgresql.org/docs/8.4/interactive/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RE...
>
>CREATE OR REPLACE FUNCTION public.next_bill_date(d date, period
>interval,
> i interval)
> RETURNS SETOF date
> AS $function$
> DECLARE
> max_date date;
> due_date date;
> BEGIN
> max_date := CURRENT_DATE + i;
> due_date := d;
> WHILE due_date + period <= max_date LOOP
> RETURN NEXT due_date; -- add d to the result set
> due_date := due_date + period;
> END LOOP;
> RETURN; -- exit function
> END;
> $function$ language plpgsql;
> testdb=# select next_bill_date('2011-06-11', '2 week', '3 month');
next_bill_date
> ----------------
> 2011-06-11
> 2011-06-25
> 2011-07-09
> 2011-07-23
> 2011-08-06
> 2011-08-20
Almost, but not quite - the d parameter is a bill's "start date",
and the function shouldn't show dates in the past. So, when the
above function is called with say '2011-06-01' as the beginning
date, the function will happily return '2011-06-01' in the result
set, even though it's in the past.
I've modified it a bit. I renamed the function arguments to be a
bit more descriptive, did a little more math, and added an IF
statement to not return any dates in the past:
CREATE OR REPLACE FUNCTION public.next_bill_date(d date, frequency
interval, daterange interval)
RETURNS SETOF date
AS $function$
DECLARE
max_date date;
due_date date;
BEGIN
-- We need to add the epoch date and daterange together, to
-- get the "max_date" value. However, this would cause us
-- to lose the last due date in the result set. Add one more
-- frequency to it so we don't lose that.
max_date := CURRENT_DATE + frequency + daterange;
due_date := d;
WHILE due_date + frequency <= max_date LOOP
-- Don't include dates in the past - we only want future
-- due dates for bills.
IF due_date >= CURRENT_DATE
THEN
RETURN NEXT due_date;
END IF;
due_date := due_date + frequency;
END LOOP;
RETURN; -- exit function
This appears to work properly:
SELECT next_bill_date( '2011-06-01', '2 weeks', '3 months' );
next_bill_date
----------------
2011-06-15
2011-06-29
2011-07-13
2011-07-27
2011-08-10
2011-08-24
2011-09-07
(7 rows)
Thanks for all your help! I'm not at all experienced with plpgsql,
so this was very much appreciated. :)
Benny
--
"You were doing well until everyone died."
-- "God", Futurama
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2011-06-11 22:18 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-06-11 04:28 Returning a set of dates C. Bensend <benny@bennyvision.com>
2011-06-11 05:11 ` Samuel Gendler <sgendler@ideasculptor.com>
2011-06-11 22:18 Re: Returning a set of dates C. Bensend <benny@bennyvision.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