Thread: skip weekends: revisited
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?
Jeff Amiel wrote: > 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. ... > What am I doing/interpreting wrong? I'm not sure about the algorithm. I can tell you, however, that such an algorithm is locale dependent. For example, in Israel the work week is Sunday-Thursday. Weekend is Friday and Saturday. I'm fairly sure that there are places around the globe in which a work-week is six days long. Shachar -- Shachar Shemesh Lingnu Open Source Consulting ltd. Have you backed up today's work? http://www.lingnu.com/backup.html
Jeff Amiel wrote: > 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). ... > What am I doing/interpreting wrong? > From the looks of it, variable date seems to calculate the first day of a week and for it to work day_of_the_week has to start from 0 so Sunday - 0, Friday - 5 (in US at least?) Andre
Andre Maasikas wrote: > From the looks of it, variable date seems to calculate > the first day of a week and for it to work day_of_the_week > has to start from 0 so Sunday - 0, Friday - 5 > (in US at least?) > > Andre That appears to be the case (0 is sunday.....) thanks for the assist!!