Thread: 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
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@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of C. Bensend
Sent: Monday, April 04, 2011 8:12 PM
To: pgsql-general@postgresql.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@postgresql.org) To make
changes to your subscription:
http://www.postgresql.org/mailpref/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
generate_series(date '2001-01-05', date '2020-12-31', interval '2 weeks') will return every payday from jan 5 2001 to the end of 2020 (assuming the 5th was payday, change the start to jan 12 if that was instead).
> 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) Ah, thank you, David. This gives me some good knowledge that I was missing! I know about %, but I was fumbling a bit with it in plpgsql, and your examples helped. Thanks! Benny -- "Hairy ape nads." -- Colleen, playing Neverwinter Nights
> 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. Great stuff, Andy! Thank you for this - this function gives me a lot of great hints about functions in plpgsql. Very useful indeed. Benny -- "Hairy ape nads." -- Colleen, playing Neverwinter Nights
> generate_series(date '2001-01-05', date '2020-12-31', interval '2 weeks') > > > will return every payday from jan 5 2001 to the end of 2020 (assuming > the 5th was payday, change the start to jan 12 if that was instead). And THERE is the winner. I feel like an idiot for not even considering generate_series(). Thanks a bunch, John! This will do nicely! Benny -- "Hairy ape nads." -- Colleen, playing Neverwinter Nights
On Mon, Apr 4, 2011 at 7:12 PM, C. Bensend <benny@bennyvision.com> 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
first, let's fix your function definition. I would advise you to take
in both the base pay date (so we know which 'every other' to use) and
the epoch so you don't have to read it from the database in the
function. Why do that? you can make your function immutable.
CREATE OR REPLACE FUNCTION is_payday(
d DATE,
base_date DATE,
payroll_epoch INT) RETURNS BOOLEAN AS $$
SELECT (select extract('j' from $1)::int - select extract('j' from
$2)::int) % $3 = 0;
$$ LANGUAGE sql IMMUTABLE;
By making this function sql and immutable, you give the database more
ability to inline it into queries which can make a tremendous
performance difference in some cases. You can also index based on it
which can be useful.
By pulling out julian days, we can do simple calculation based on days
(julian day, not to be confused with julian calendar, is kinda sorta
like epoch for days. While it doesn't really apply to this toy
example, a key thing to remember if if trying to write high
performance pl/pgsql is to separate stable/immutable, and volatile
elements. Also, use sql, not plpgsql in trivial functions.
If you don't want to select out your option in every query, I'd advise
making an option() function which wraps the trivial select:
select is_payday(some_date, option('base_date'), option('payroll_epoch');
The 'option' function should be stable.
merlin
> By making this function sql and immutable, you give the database more > ability to inline it into queries which can make a tremendous > performance difference in some cases. You can also index based on it > which can be useful. Very nice, Merlin. These aren't really a concern in my case as I'm the only one accessing the app (and hence, performance isn't an issue), but it's good to see a better way to do things. The PostgreSQL community really is top notch. Benny -- "Hairy ape nads." -- Colleen, playing Neverwinter Nights