From: | Jasen Betts <jasen(at)xnet(dot)co(dot)nz> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: finding if a period is multiples of a given interval |
Date: | 2013-02-03 04:08:36 |
Message-ID: | kekns4$net$1@gonzo.reversiblemaps.ath.cx |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 2013-01-28, c k <shreeseva(dot)learning(at)gmail(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Jasen Betts | 2013-02-03 04:10:37 | Re: finding if a period is multiples of a given interval |
Previous Message | Jasen Betts | 2013-02-03 03:03:34 | Re: COPY table to file missing quotation marks |