Re: Plpgsql function to compute "every other Friday" - Mailing list pgsql-general

On 04/04/2011 07:12 PM, C. Bensend wrote:
>
> Hey folks,
>
>     So, I'm working on a little application to help me with my
> budget.  Yeah, there are apps out there to do it, but I'm having
> a good time learning some more too.  :)
>
>     I get paid every other Friday.  I thought, for scheduling
> purposes in this app, that I would take a stab at writing a plpgsql
> function to determine if a given date is a payday.  Here is what I
> have so far:
>
>
> CREATE OR REPLACE FUNCTION is_payday( d DATE ) RETURNS BOOLEAN AS $$
>
>          DECLARE epoch DATE;
>                  days_since_epoch INTEGER;
>                  mult FLOAT8;
>                  ret BOOLEAN := FALSE;
>
> BEGIN
>
>          SELECT INTO epoch option_value
>                  FROM options WHERE option_name = 'payroll_epoch';
>
>          SELECT INTO days_since_epoch ( SELECT CURRENT_DATE - d);
>
>          *** here's where I'm stuck ***
>
>          RETURN ret;
>
> END;
> $$ LANGUAGE plpgsql;
>
>
>     OK.  So, I have a "starting" payday (payroll_epoch) in an options
> table.  That is the first payday of the year.  I then calculate the
> number of days between that value and the date I pass to the function.
> Now I need to calculate whether this delta (how many days since
> epoch) is an even multiple of 14 days (the two weeks).
>
>     I have no idea how to do that in plpgsql.  Basically, I need to
> figure out if the date I pass to the function is a payday, and if
> it is, return TRUE.
>
>     I would very much appreciate any help with this last bit of math
> and syntax, as well as any advice on whether this is a reasonable
> way to attack the problem.  And no - this isn't a homework
> assignment.  :)
>
> Thanks folks!
>
> Benny
>
>

Not sure if your needs are like mine, but here is the function I use.  It stores the date in a config table, and rolls
itforward when needed.  It also calculates it from some "know payroll date", which I'm guessing was near when I wrote
it? (I'm not sure why I choose Nov 16 2008.)  for me, this procedure is called a lot, and the things calling it expect
itto roll into the next pay period.  Not sure if it'll work for you, but might offer some ideas. 



CREATE OR REPLACE FUNCTION startpayperiod()
  RETURNS date
  LANGUAGE plpgsql
AS $function$
declare
         st date;
         last date;
         needins boolean;
begin
         select avalue::date into st from config where akey = 'startPayPeriod';
         if (st is null) then
                 st := '2008.11.16';
                 needins := true;
         else
                 needins := false;
         end if;
         -- find the end of the pp
         last := st + interval '13 days';
         if (current_date > last) then
                 -- raise notice 'need update';
                 loop
                         last := st;
                         st := st + interval '2 weeks';
                         if current_date < st then
                                 exit;
                         end if;
                 end loop;
                 st := last;
                 if needins then
                         insert into config(akey, avalue) values('startPayPeriod', st::text);
                 else
                         update config set avalue = st::text where akey = 'startPayPeriod';
                 end if;
         end if;
         return st;
end; $function$


-Andy