skip weekends: revisited
От | Jeff Amiel |
---|---|
Тема | skip weekends: revisited |
Дата | |
Msg-id | 4235982D.2060802@istreamimaging.com обсуждение исходный текст |
Ответы |
Re: skip weekends: revisited
Re: skip weekends: revisited |
Список | 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?
В списке pgsql-general по дате отправления: