Обсуждение: finding if a period is multiples of a given interval

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.klaver@gmail.com

On 01/28/2013 07:17 AM, c k wrote:
> 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.

I changed the dates to match my date_style.

test=> select age('05/15/2010'::date ,'04/01/2010'::date)
test-> ;
       age
---------------
  1 mon 14 days
(1 row)

test=> select date_part('days', age('05/15/2010'::date
,'04/01/2010'::date));
  date_part
-----------
         14
(1 row)


test=> select age('06/01/2010'::date ,'04/01/2010'::date)
;
   age
--------
  2 mons
(1 row)

test=> select date_part('days', age('06/01/2010'::date
,'04/01/2010'::date));
  date_part
-----------
          0
(1 row)


>
> Regards,
> C P Kulkarni
>
>



--
Adrian Klaver
adrian.klaver@gmail.com

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?

First, you need to define, in a way that meets all your
business/system/financial/whatever requirements, exactly what each
interval means. The way PostgreSQL interprets certain ambiguities may be
different than what you need. It has also changed over time. Many
versions back, for instance, subtracting two dates that crossed a
daylight saving time change would give you 23 or 25 hours but now
returns 1 day. You also have to have a good understanding of when and
how casting takes place and how the different data-types behave:

steve@[local] => select '2013-03-11'::timestamptz -
'2013-03-10'::timestamptz;
  ?column?
----------
  23:00:00


steve@[local] => select '2013-03-11'::date - '2013-03-10'::date;
  ?column?
----------
         1

Same thing with operator precedence and ordering:

steve@[local] => select '2013-03-31'::date - '1 month'::interval + '1
month'::interval;
       ?column?
---------------------
  2013-03-28 00:00:00

steve@[local] => select '2013-03-31'::date + '1 month'::interval - '1
month'::interval;
       ?column?
---------------------
  2013-03-30 00:00:00

Is the end of one month to the end of the next month one-month? And can
the user reverse the dates? If end-of-February (2013-02-28) is one month
before end-of-March (2013-03-31) then what is the span of time between
Feb 28 and March 28? 29? 30?

Before anyone can help with implementation you need to provide a
detailed definition of your operations including special cases.

Cheers,
Steve

On 2013-01-30, c k <shreeseva.learning@gmail.com> wrote:
> --bcaec54eebba86ab5904d4815b33
> Content-Type: text/plain; charset=UTF-8
>
> Thanks.
> After some hacking it solved my problems using
> select date_part('days', age('06/01/2010'::date ,'04/01/2010'::date));
>

??? you can't get there from here.

--
⚂⚃ 100% natural

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