From: | "David Johnston" <polobo(at)yahoo(dot)com> |
---|---|
To: | "'C(dot) Bensend'" <benny(at)bennyvision(dot)com>, <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Plpgsql function to compute "every other Friday" |
Date: | 2011-04-05 00:39:43 |
Message-ID: | 02c501cbf329$f54946e0$dfdbd4a0$@yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of C. Bensend
Sent: Monday, April 04, 2011 8:12 PM
To: pgsql-general(at)postgresql(dot)org
Subject: [GENERAL] 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(at)postgresql(dot)org) To make
changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
From | Date | Subject | |
---|---|---|---|
Next Message | Andy Colson | 2011-04-05 00:54:53 | Re: Plpgsql function to compute "every other Friday" |
Previous Message | C. Bensend | 2011-04-05 00:12:04 | Plpgsql function to compute "every other Friday" |