| Bug #28389 | Nr of workdays calculation like excel | ||
|---|---|---|---|
| Submitted: | 12 May 2007 16:45 | Modified: | 3 Oct 2009 18:53 |
| Reporter: | Ivo van Kamp | Email Updates: | |
| Status: | No Feedback | Impact on me: | |
| Category: | MySQL Server: User-defined functions ( UDF ) | Severity: | S4 (Feature request) |
| Version: | 5.1 | OS: | Linux (Xubuntu 7.04) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | business days, Contribution, DATEDIFF, networkdays, workdays | ||
[12 May 2007 17:29]
MySQL Verification Team
Thank you for the bug report feature request.
[12 May 2007 22:10]
Ivo van Kamp
Bugfix. Misinterpreted day of week..
Attachment: workdays.c (application/octet-stream, text), 4.81 KiB.
[12 May 2007 22:27]
Ivo van Kamp
Thank you for having me. If you like I can try to write a patch to create a workdays function.
[19 May 2007 6:38]
Ivo van Kamp
Patch file against 5.0.21 to add a workdays function. Apply with patch -Np0 -i [patchfile] in the root dir of the source tree.
Attachment: mysql-5.0.21.workdays.patch (text/x-diff), 3.68 KiB.
[19 May 2007 6:55]
Ivo van Kamp
Patch tested on MySQL 5.0.21, Linux Xubuntu 7.04.
[19 May 2007 7:01]
Ivo van Kamp
mysql> select workdays('2007-05-13','2007-05-19');
+-------------------------------------+
| workdays('2007-05-13','2007-05-19') |
+-------------------------------------+
| 5 |
+-------------------------------------+
1 row in set (0.00 sec)
mysql> select workdays('2007-05-19','2007-05-13');
+-------------------------------------+
| workdays('2007-05-19','2007-05-13') |
+-------------------------------------+
| -5 |
+-------------------------------------+
1 row in set (0.00 sec)
[19 May 2007 17:43]
Ivo van Kamp
Added tag "Contribution"
[3 Sep 2009 18:53]
Liz Drachnik
Hello Ivo In order for us to continue the process of reviewing your contribution to MySQL - We need you to review and sign the Sun|MySQL contributor agreement (the "SCA") The process is explained here: http://forge.mysql.com/wiki/Sun_Contributor_Agreement Getting a signed/approved SCA on file will help us facilitate your contribution-- this one, and others in the future. Thank you !
[3 Oct 2009 23:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
Description: A calc_workdays() function in C which uses the MySQL functions calc_daynr and calc_weekday. How to repeat: /* Calculate nr of day since year 0 in new date-system (from 1615) */ long calc_daynr(int year,int month,int day) { long delsum; int temp; //DBUG_ENTER("calc_daynr"); if (year == 0 && month == 0 && day == 0) return (0); /* Skip errors */ delsum= (long) (365L * year+ 31*(month-1) +day); if (month <= 2) year--; else delsum-= (long) (month*4+23)/10; temp=(int) ((year/100+1)*3)/4; return (delsum+(int) year/4-temp); } /* calc_daynr */ /* Calc weekday from daynr */ /* Returns 0 for monday, 1 for tuesday .... */ int calc_weekday(long daynr,int sunday_first_day_of_week) { //DBUG_ENTER("calc_weekday"); return ((int) ((daynr + 5L + (sunday_first_day_of_week ? 1L : 0L)) % 7)); } /* Calc workdays like excel */ /* nr of weekends = nr of days / 7 nr of workdays = nr of days - nr of weekends The remainder of the division of nr of days / 7 could fully or partly comprise a weekend. Example: 12345671234567123456712345671234567123456712345671234567123456712345671234567 1234567890123456789 (2 weeks + 5 days) 12345678901234 (2 weeks = 14 days ) 56789 (the remainder of 5 days begins on the same day as the start date) The first line in the table below shows that for a remainder of 6 days, when starting on the first day of the week, there is one weekendday. If startday = 7 then there is only 1 weekendday If nr of remaining days - (7 - day of week) + 1 = < 1 => 0 weekenddays = 1 => 1 weekendday >1 => 2 weekenddays (a saturday and a sunday) Nr of Nr of Calculation attempt remaining Startday weekend days days 6 days day 1 1 7-1=6 6-6+1 = 1 1 6 days day 2 2 7-2=5 6-5+1 = 2 6 days day 3 2 7-3=4 6-4+1 = 3 6 days day 4 2 7-4=3 6-3+1 = 4 6 days day 5 2 7-5=2 6-2+1 = 5 6 days day 6 2 7-6=1 6-1+1 = 6 6 days day 7 1 7-7=0 6-0+1 = 7 1 5 days day 1 0 7-1=6 5-6+1 = 0 0 5 days day 2 1 7-2=5 5-5+1 = 1 1 5 days day 3 2 7-3=4 5-4+1 = 2 5 days day 4 2 7-4=3 5-3+1 = 3 5 days day 5 2 7-5=2 5-2+1 = 4 5 days day 6 2 7-6=1 5-1+1 = 5 5 days day 7 1 7-7=0 5-0+1 = 6 1 4 days day 1 0 4 days day 2 0 4 days day 3 1 4 days day 4 2 4 days day 5 2 4 days day 6 2 4 days day 7 1 3 days day 1 0 3 days day 2 0 3 days day 3 0 3 days day 4 1 3 days day 5 2 3 days day 6 2 3 days day 7 1 2 days day 1 0 2 days day 2 0 2 days day 3 0 2 days day 4 0 2 days day 5 1 2 days day 6 2 2 days day 7 1 1 days day 1 0 1 days day 2 0 1 days day 3 0 1 days day 4 0 1 days day 5 0 1 days day 6 1 1 days day 7 1 */ int calc_workdays(int y1, int m1, int d1, int y2, int m2, int d2) { int days1 = calc_daynr(y1, m1, d1); int days2 = calc_daynr(y2, m2, d2); int nrofdays = days2 - days1; if (nrofdays==0) return 1; // e.g. monday - monday = 1 day int neg_nrofdays = (nrofdays < 0); int dayofweek = calc_weekday((neg_nrofdays ? days2 : days1), 0); nrofdays = abs(nrofdays)+1; // e.g. diff mon-tue = 1, but takes 2 days int nrofweekends = floor(nrofdays / 7); int mod = nrofdays % 7; int add = 0; // Correction when the remainder (part of a week) // fully or partly comprises a weekend. if (mod) { if ((dayofweek)==7) add=1; else { add = mod - (7 - dayofweek) + 1; if (add<0) add = 0; if (add>1) add = 2; } } int workdays = nrofdays - ((nrofweekends*2) + add); return (neg_nrofdays ? -workdays : workdays); } int main (void) { // Print nr of workdays between May 1th, 2007 and Apr 16th, 2007 //printf("Nr of workdays: %d\n", calc_workdays(2007, 5, 1, 2007, 4, 16)); // Print nr of workdays between May 13th, 2007 and May 19th, 2007 printf("Nr of workdays: %d\n", calc_workdays(2007, 5, 13, 2007, 5, 19)); } Suggested fix: mysql> SELECT WORKDAYS('2007-5-13','2007-5-19'); -> 5 mysql> SELECT WORKDAYS('2007-5-19','2007-5-13'); -> -5