On 2013-01-28, c k <shreeseva.learning@gmail.com> wrote:
> --bcaec5014c15b72ffb04d459337f
> Content-Type: text/plain; charset=UTF-8
>
> 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.
are these set in stone or are they merley examples
> Now, I have to find if the period of given two dates (p_todate -
> p_fromdate) is multiples of the given interval or not?
exact integer multiples?
do you want to know how many?
> 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.
???
that's '36 months - 1 day' or '37 months - 31 days'
or several other variants none of which is a multiple of your example period.
might I suggest you drop the "-1 day" part and add one to p_enddate
(possibly after the user enters it)
then a month is '1 month' and a quarter is '3 months'
> 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?
unbounded binary search to find the numerator? write a procedural
function that attepts to find the multiple of interval that satisfies
the equation...
ie find N that satisfies
p_fromdate + N * p_interval = p_todate
--
ââ 100% natural
