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 it forward 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 it to 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
--
Sent via pgsql-general mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
