Thread: finding if a period is multiples of a given interval
Hi,
I have two variables in pl/pgsql function. 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
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.On Mon, Jan 28, 2013 at 8:36 PM, Adrian Klaver <adrian.klaver@gmail.com> wrote:
Not quite sure what you are trying to accomplish.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?
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
Thanks.
After some hacking it solved my problems using select date_part('days', age('06/01/2010'::date ,'04/01/2010'::date));
On Mon, Jan 28, 2013 at 9:32 PM, Adrian Klaver <adrian.klaver@gmail.com> wrote:
On 01/28/2013 07:17 AM, c k wrote:I changed the dates to match my date_style.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.
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 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