pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSQL Challenge: Skip Weekends
5+ messages / 3 participants
[nested] [flat]
* SQL Challenge: Skip Weekends
@ 2002-06-21 00:13 Josh Berkus <josh@agliodbs.com>
2002-06-21 05:32 ` Re: SQL Challenge: Skip Weekends Joe Conway <mail@joeconway.com>
2002-06-21 17:46 ` Re: SQL Challenge: Skip Weekends Jean-Luc Lachance <jllachan@nsd.ca>
0 siblings, 2 replies; 5+ messages in thread
From: Josh Berkus @ 2002-06-21 00:13 UTC (permalink / raw)
To: pgsql-sql
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:
Idea #1: Use a reference table
1. Using a script, generate a table of all weekends from 2000 to 2050.
2. Increase the interval by the number of weekends that fall in the relevant
period.
Idea #2: Some sort of calculation using 5/7 of the interval, adjusted
according to the day of the week of our starting date. My head hurts trying
to figure this one out.
--
-Josh Berkus
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: SQL Challenge: Skip Weekends
2002-06-21 00:13 SQL Challenge: Skip Weekends Josh Berkus <josh@agliodbs.com>
@ 2002-06-21 05:32 ` Joe Conway <mail@joeconway.com>
2002-06-21 19:31 ` Re: SQL Challenge: Skip Weekends Josh Berkus <josh@agliodbs.com>
1 sibling, 1 reply; 5+ messages in thread
From: Joe Conway @ 2002-06-21 05:32 UTC (permalink / raw)
To: josh@agliodbs.com; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: SQL Challenge: Skip Weekends
2002-06-21 00:13 SQL Challenge: Skip Weekends Josh Berkus <josh@agliodbs.com>
2002-06-21 05:32 ` Re: SQL Challenge: Skip Weekends Joe Conway <mail@joeconway.com>
@ 2002-06-21 19:31 ` Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 5+ messages in thread
From: Josh Berkus @ 2002-06-21 19:31 UTC (permalink / raw)
To: Joe Conway <mail@joeconway.com>; josh@agliodbs.com; +Cc: pgsql-sql
Joe,
> 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)
Thank you. Once again, you come to the rescue when I'm stuck. I'll
try your solution and Jean-Luc's, and see which works better/faster.
And report back.
-Josh Berkus
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: SQL Challenge: Skip Weekends
2002-06-21 00:13 SQL Challenge: Skip Weekends Josh Berkus <josh@agliodbs.com>
@ 2002-06-21 17:46 ` Jean-Luc Lachance <jllachan@nsd.ca>
2002-06-21 19:29 ` Re: SQL Challenge: Skip Weekends Josh Berkus <josh@agliodbs.com>
1 sibling, 1 reply; 5+ messages in thread
From: Jean-Luc Lachance @ 2002-06-21 17:46 UTC (permalink / raw)
To: josh@agliodbs.com; +Cc: pgsql-sql
here is the algorithm:
date := now - day_of_the_week
interval := interval + day_of_the_week
date := date + int( interval/5)x7 + ( interval mod 5)
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:
>
> Idea #1: Use a reference table
> 1. Using a script, generate a table of all weekends from 2000 to 2050.
> 2. Increase the interval by the number of weekends that fall in the relevant
> period.
>
> Idea #2: Some sort of calculation using 5/7 of the interval, adjusted
> according to the day of the week of our starting date. My head hurts trying
> to figure this one out.
>
> --
> -Josh Berkus
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: SQL Challenge: Skip Weekends
2002-06-21 00:13 SQL Challenge: Skip Weekends Josh Berkus <josh@agliodbs.com>
2002-06-21 17:46 ` Re: SQL Challenge: Skip Weekends Jean-Luc Lachance <jllachan@nsd.ca>
@ 2002-06-21 19:29 ` Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 5+ messages in thread
From: Josh Berkus @ 2002-06-21 19:29 UTC (permalink / raw)
To: Jean-Luc Lachance <jllachan@nsd.ca>; josh@agliodbs.com; +Cc: pgsql-sql
Jean-Luc,
> date := now - day_of_the_week
> interval := interval + day_of_the_week
> date := date + int( interval/5)x7 + ( interval mod 5)
Merci, merci, merci!
-Josh
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2002-06-21 19:31 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-06-21 00:13 SQL Challenge: Skip Weekends Josh Berkus <josh@agliodbs.com>
2002-06-21 05:32 ` Joe Conway <mail@joeconway.com>
2002-06-21 19:31 ` Josh Berkus <josh@agliodbs.com>
2002-06-21 17:46 ` Jean-Luc Lachance <jllachan@nsd.ca>
2002-06-21 19:29 ` Josh Berkus <josh@agliodbs.com>
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