It is a very simplistic approach since you do not take into account holidays. But if it meets your needs what you want is the modulo operator ( "%"; "mod(x,y)" is the equivalent function ) which performs division but returns only the remainder. N % 14 = [a number between 0 and (14 - 1)] N = 7; 7 % 14 = 7 (0, 7 remainder) N = 14; 14 % 14 = 0 (1, 0 remainder) N = 28; 28 % 14 = 0 (2, 0 remainder) N = 31; 31 % 14 = 3 (2, 3 remainder) If you KNOW the epoch date you are using is a Friday then you have no need for CURRENT_DATE since you are passing in a date to check as a function parameter. I'll have to leave it to you or others to address the specific way to integrate the modulo operator/function into the algorithm. David J. -----Original Message----- From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of C. Bensend Sent: Monday, April 04, 2011 8:12 PM To: pgsql-general@xxxxxxxxxxxxxx Subject: Plpgsql function to compute "every other Friday" 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 -- "Hairy ape nads." -- Colleen, playing Neverwinter Nights -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
