From: Jean-Luc Lachance <jllachan@nsd.ca>
To: josh@agliodbs.com
Cc: pgsql-sql@postgresql.org
Subject: Re: SQL Challenge: Skip Weekends
Date: Fri, 21 Jun 2002 13:46:19 -0400
Message-ID: <3D13666B.AC687A4A@nsd.ca> (raw)
References: <200206201713.03801.josh@agliodbs.com>
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
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: jllachan@nsd.ca, josh@agliodbs.com
Subject: Re: SQL Challenge: Skip Weekends
In-Reply-To: <3D13666B.AC687A4A@nsd.ca>
* 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