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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/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.klaver@gmail.com>wrote:

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Thanks.
After some hacking it solved my problems using
select date_part('days', age('06/01/2010'::date ,'04/01/2010'::date));

Regards,

On Mon, Jan 28, 2013 at 9:32 PM, Adrian Klaver <adrian.klaver@gmail.com>wrote:

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general