Re: Plpgsql function to compute "every other Friday"
От | Andy Colson |
---|---|
Тема | Re: Plpgsql function to compute "every other Friday" |
Дата | |
Msg-id | 4D9A685D.4040001@squeakycode.net обсуждение исходный текст |
Ответ на | Plpgsql function to compute "every other Friday" ("C. Bensend" <benny@bennyvision.com>) |
Ответы |
Re: Plpgsql function to compute "every other Friday"
|
Список | 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
В списке pgsql-general по дате отправления:
Предыдущее
От: "David Johnston"Дата:
Сообщение: Re: Plpgsql function to compute "every other Friday"