Обсуждение: Adding to a date
I was beginning to create some financial functions for calculating Future Values, PV's, etc -for basic retirement planning. example: CREATE OR REPLACE FUNCTION lotsa.FVPMT(payment double precision, interestRate double precision, periods double precision) RETURNS double precision AS $BODY$ BEGIN return payment*(pow(1+interestRate, periods)-1)/interestRate; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE periods seems to be a problem. Usually, I'd be getting birth_date from a file, but I'm not finding a way to add 65 years - or 67 or whatever, to get the period between now (or an assumed date) and retirement. I'm hoping there is a way to: 1) Date of birth + xx years - with a result of date (Result 1) 2) Subtract now or an assumed date from Result 1 to get Result 2 3) Divide by ~ 365.25 for years, which would be 'periods' in the function. I've gotten around it, but it sure seems pretty messy: Greatest(FVPMT(test_fv.pmt, test_fv.i_rate, (23741.25-(test_fv.start_date-test_fv.dob))/365.25),0) where the 23741.25 is 65* 365.25. Any help would be appreciated. I've read all of what would seem to be appropriate. Doesn't mean I understood it, but I read it. I'm not concerned about being off by a day or 2. Small potatoes compared to the variations rate of return will undergo. Thanks
Sorry, forgot to include the group in my reply... ---------- Forwarded message ---------- From: Harold A. Giménez Ch. <harold.gimenez@gmail.com> Date: Mon, Jun 30, 2008 at 5:51 PM Subject: Re: [NOVICE] Adding to a date To: Mike Ellsworth <younicycle@gmail.com> Intervals might help...for example: timestamp '2001-09-28 01:00' + interval '23 hours' = timestamp '2001-09-29 00:00:00' Taken from here: http://www.postgresql.org/docs/8.3/interactive/functions-datetime.html On Mon, Jun 30, 2008 at 5:44 PM, Mike Ellsworth <younicycle@gmail.com> wrote: > > I was beginning to create some financial functions for calculating > Future Values, PV's, etc -for basic retirement planning. > example: > > CREATE OR REPLACE FUNCTION lotsa.FVPMT(payment double precision, > interestRate double precision, periods double precision) > RETURNS double precision AS > $BODY$ > BEGIN > return payment*(pow(1+interestRate, periods)-1)/interestRate; > END; > $BODY$ > LANGUAGE 'plpgsql' VOLATILE > > periods seems to be a problem. Usually, I'd be getting birth_date > from a file, but I'm not finding a way to > add 65 years - or 67 or whatever, to get the period between now (or an > assumed date) and retirement. > > I'm hoping there is a way to: > 1) Date of birth + xx years - with a result of date (Result 1) > 2) Subtract now or an assumed date from Result 1 to get Result 2 > 3) Divide by ~ 365.25 for years, which would be 'periods' in the function. > > I've gotten around it, but it sure seems pretty messy: > Greatest(FVPMT(test_fv.pmt, test_fv.i_rate, > (23741.25-(test_fv.start_date-test_fv.dob))/365.25),0) > > where the 23741.25 is 65* 365.25. > > Any help would be appreciated. I've read all of what would seem to be > appropriate. > Doesn't mean I understood it, but I read it. > > I'm not concerned about being off by a day or 2. > Small potatoes compared to the variations rate of return will undergo. > > Thanks > > -- > Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-novice
Mike Ellsworth wrote:
> I was beginning to create some financial functions for calculating
> Future Values, PV's, etc...
>
> I'm hoping there is a way to:
> 1) Date of birth + xx years - with a result of date (Result 1)
> 2) Subtract now or an assumed date from Result 1 to get Result 2
>
Problems like 1 and 2 are easy - use "intervals".
select '1943-06-30'::date + '65 years'::interval;
2008-06-30 00:00:00
select current_date + '65 years'::interval;
2073-06-30 00:00:00
> 3) Divide by ~ 365.25 for years, which would be 'periods' in the function.
>
select ('2008-06-30'::date - '1943-06-30'::date)/365.25;
65.0020533880903491
Though there may be a better method for this. I'd need to poke around in
the date arithmetic.
Cheers,
Steve
>> I was beginning to create some financial functions for calculating
>> Future Values, PV's, etc...
>>
>> I'm hoping there is a way to:
>> 1) Date of birth + xx years - with a result of date (Result 1)
>> 2) Subtract now or an assumed date from Result 1 to get Result 2
>>
>
> Problems like 1 and 2 are easy - use "intervals".
>
> select '1943-06-30'::date + '65 years'::interval;
> 2008-06-30 00:00:00
>
> select current_date + '65 years'::interval;
> 2073-06-30 00:00:00
>
>
>> 3) Divide by ~ 365.25 for years, which would be 'periods' in the function.
>>
>
> select ('2008-06-30'::date - '1943-06-30'::date)/365.25;
>
> 65.0020533880903491
>
> Though there may be a better method for this. I'd need to poke around in the
> date arithmetic.
>
Here's what I came up with. We're trying to make this simple for
users, so we'll need to simplify the date & interval (periods) portion
of the FVPMT function a little .. but this works.
SELECT
ages.dob AS ages_dob, to_char(greatest(round(FVPMT(ages.annual*.04,
.06, (((ages.dob + interval '65 years')::date)- date
'7/1/2008')/365.25)),0), '$FMG999,999') AS fv, ages.annual AS
annual_wage
FROM
"test"."ages"
Thanks for the help from all.