skip weekends: revisited

From: Jeff Amiel <jamiel(at)istreamimaging(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: skip weekends: revisited
Date: 2005-03-14 13:57:01
Message-ID: 4235982D.2060802@istreamimaging.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Back in 2002, Jean-Luc Lachance gave a nifty algorithm
(http://archives.postgresql.org/pgsql-sql/2002-06/msg00301.php) for
determining the resulting date given a starting date and number of
'workdays' in the future. The trick was that weekends (Saturday and
Sunday) could not be counted.

The algorithm looks like this:

date := now - day_of_the_week
interval := interval + day_of_the_week
date := date + int( interval/5)x7 + ( interval mod 5)

However, when I attempted to implement it, I found the answers I received to be problematic.
I'm sure the issue is in my interpretation of the algorithm, but I can't quite figure it out.

Let's take an example. Starting on March 11, 2005, what date is 4 'workdays' in the future?

Step one:
date := now - day_of_the_week
March 5th = March 11th - 6 (6 is the day of week for Friday....march 11th).

Step two:
interval = interval + day of the week
10 = 4 + 6 (4 is the interval)

After this point, the date field is now March 5th and the interval is now 10.

Step three:
date := date + int( interval/5)x7 + ( interval mod 5)
March 19th = March 5th + 14 + 0

End result.....March 19th.
But March 19th is not correct (heck, it's a Saturday!). It should be March 17th.

What am I doing/interpreting wrong?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Miroslav Šulc 2005-03-14 14:05:30 Re: free space map settings
Previous Message go 2005-03-14 13:52:58 free space map settings