MySQL: Calculate the difference between Date/Times - only during M-F "Work week"

Question

I need to calculate a difference between a starting date/time and an ending date/time. But, I only want to do this for the 5-day work week (exclude Sat/Sun as days). What is the best way to do this? My thought is that from the date, I'll have to get the day of the week and if it is a weekday, then I add to the accumulator. If it's not, then I don't add anything.

I'm sure someone has done this before, but I couldn't seem to find anything searching. Any links or other help would be very useful.

Many thanks,

Bruce

Answer 1

DAYOFWEEK returns 1 for Sunday and 7 for Saturday. I'm not sure how your schema is set up, but this will perform a TIMEDIFF of two dates that are on a Monday - Friday work week.

select TIMEDIFF(date1,date2) from table 
where DAYOFWEEK(date1) not in (1,7) and DAYOFWEEK(date2) not in (1,7)

MySQL DATE/TIME functions

EDIT: From Bruce's comment about holidays. If you have a table full of holiday dates, something like this would work to exclude processing those days:

select TIMEDIFF(date1,date2) from table 
where date1 not in (select holiday from holiday_table) and
date2 not in (select holiday from holiday_table) and
DAYOFWEEK(date1) not in (1,7) and DAYOFWEEK(date2) not in (1,7)

Answer 2

NETWORKDAYS() "Returns the number of whole working days between start_date and end_date. Working days exclude weekends and any dates identified in holidays. Use NETWORKDAYS to calculate employee benefits that accrue based on the number of days worked during a specific term." according to the Excel 2007 help file.

The "between" description is a bit inaccurate because it includes the start and end dates, i.e. networkdays(21-01-2010. 22-01-2010) = 2. It also takes no account of times.

Here's a function in PHP that will give the same results. It doesn't work properly if the end date is less than the start date, nor does do anything about holidays (see below the function).

function networkdays($startdate, $enddate)
  {
     $start_array = getdate(strtotime($startdate));
     $end_array = getdate(strtotime($enddate));

     // Make appropriate Sundays
     $start_sunday = mktime(0, 0, 0, $start_array[mon], $start_array[mday]+(7-$start_array[wday]),$start_array[year]);
     $end_sunday = mktime(0, 0, 0, $end_array[mon], $end_array[mday]- $end_array[wday],$end_array[year]);

     // Calculate days in the whole weeks
     $week_diff = $end_sunday - $start_sunday;
     $number_of_weeks = round($week_diff /604800); // 60 seconds * 60 minutes * 24 hours * 7 days = 1 week in seconds
     $days_in_whole_weeks = $number_of_weeks * 5;

     //Calculate extra days at start and end
     //[wday] is 0 (Sunday) to 7 (Saturday)
     $days_at_start = 6 - $start_array[wday];
     $days_at_end = $end_array[wday];

     $total_days = $days_in_whole_weeks + $days_at_start + $days_at_end;

     return $total_days;
}

To take holidays into account, you'd have to work out the number of days using this function, then use a query like

Select count (holiday_date) from holidays
where holiday_date between start_date and end_date
and DAYOFWEEK(holiday_date) not in (1,7)

Be careful that there isn't a problem with the end_date being treated as 00:00 (i.e. first thing in the morning) - you may have to condition it to be 23:59:59 so that it works properly. It all depends on how your holidays are stored.

To return the holidays in the same time period and subtract that from the number you first thought of.