From: Joe Conway <mail@joeconway.com>
To: josh@agliodbs.com
Cc: pgsql-sql@postgresql.org
Subject: Re: SQL Challenge: Skip Weekends
Date: Thu, 20 Jun 2002 22:32:47 -0700
Message-ID: <3D12BA7F.1090209@joeconway.com> (raw)
References: <200206201713.03801.josh@agliodbs.com>
Josh Berkus wrote:
> Folks,
>
> Hey, I need to write a date calculation function that calculates the
> date after a number of *workdays* from a specific date. I pretty
> much have the "skip holidays" part nailed down, but I don't have a
> really good way to skip all weekends in the caluclation. Here's the
> ideas I've come up with:
How about this (a bit ugly, but I think it does what you want -- minus
the holidays, which you said you already have figured out):
create or replace function
get_future_work_day(timestamp, int)
returns timestamp as '
select
case when extract(dow from future_work_date) = 6
then future_work_date + ''2 days''
when extract(dow from future_work_date) = 0
then future_work_date + ''1 day''
else
future_work_date
end
from
(
select $1
+ (($2 / 5)::text || '' weeks'')
+ (($2 % 5)::text || '' days'')
as future_work_date
) as t1
' language sql;
CREATE
testslv=# select get_future_work_day('2002-06-20',27);
get_future_work_day
------------------------
2002-07-29 00:00:00-07
(1 row)
HTH,
Joe
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mail@joeconway.com, josh@agliodbs.com
Subject: Re: SQL Challenge: Skip Weekends
In-Reply-To: <3D12BA7F.1090209@joeconway.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox