Postgres Pro
Downloads
Home   >   mailing lists

Thread: Adding to a date

Adding to a date

From
"Mike Ellsworth"
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

Fwd: Adding to a date

From
"Harold A. Giménez Ch."
Date:
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

Re: Adding to a date

From
Steve Crawford
Date:
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

Re: Adding to a date

From
"Mike Ellsworth"
Date:
>> 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.