Re: finding if a period is multiples of a given interval

From: c k <shreeseva(dot)learning(at)gmail(dot)com>
To: Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com>
Cc: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Re: finding if a period is multiples of a given interval
Date: 2013-01-28 15:17:17
Message-ID: CAN2Y=uPKaZSNxE=Zbg5E47pJnbYkF5V5znDEE7FA4g8nHFSQqA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I know that. I have to check the period (dates entered by user) must be
correct and must be perfectly divisible by the interval given. This is a
pre-check for the interest calculation.

If user enters '01/04/2010' and '15/05/2010' as the dates, and interval as
'month' then, there are 15 days left and if the banking product is set to
calculate interest for a complete month only, then calculation can result
in wrong interest figures. So I have to check if given period is perfectly
divisible by the interval or not.

Regards,
C P Kulkarni

On Mon, Jan 28, 2013 at 8:36 PM, Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com>wrote:

> On 01/28/2013 05:24 AM, c k wrote:
> > Hi,
> > I have two variables in pl/pgsql function.
> > p_fromdate and p_todate
> >
> > I have another variable which represents intervals like day, month,
> > quarter etc.
> > p_interval as smallint, to hold values like 1,2,3, which are
> > substituted for intervals as '1 day', '1 month - 1 day', '3 months - 1
> > day' respectively.
> > Now, I have to find if the period of given two dates (p_todate -
> > p_fromdate) is multiples of the given interval or not?
> >
> > e.g. p_fromdate = '01/04/2010';
> > p_todate = '31/03/2013';
> >
> > p_interval=3 (which is a quarter).
> >
> > I need to find out if the period of ('31/03/2013' - '01/04/2010')
> > clearly multiple of a quarter and modulus = 0.
> > Important point is user can enter any dates and choose any interval to
> > check. 'Day' interval fits to any dates. For 'month' and others, number
> > of days, minutes, seconds are varying. So we can not use the fixed
> > values for them neither we can use '1 month - 1 day' or any interval in
> > division. Also we can not cast them to integers.
> >
> > How to get it done?
>
> Not quite sure what you are trying to accomplish.
> Have you looked at EXTRACT, it seems to cover some of what you describe:
>
>
> http://www.postgresql.org/docs/9.2/interactive/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)gmail(dot)com
>

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2013-01-28 16:02:22 Re: finding if a period is multiples of a given interval
Previous Message Adrian Klaver 2013-01-28 15:14:36 Re: Rules on views - Changes from 8.4 to 9.1 ?