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?

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(dot)klaver(at)gmail(dot)com