Re: 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?

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(at)[local] => select '2013-03-11'::timestamptz -
'2013-03-10'::timestamptz;
?column?
----------
23:00:00

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

Same thing with operator precedence and ordering:

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

steve(at)[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