Working With Dates In PHP

I have a date stored in a MySQL db that I need to subtract from the current date.  MY formula would look something like this:

IF(CURRENT_DATE - DB_DATE >= 6 days){

RUN A QUERY
}

The catch is, I only need to count weekdays in my subtraction.  Weekends do not need to count towards the 6 day condition.  How would I go about accomplishing this? I will post more info if needed; please let me know if I need to do so.  Thanks for reading.
thedeal56Asked:
Who is Participating?
 
Michael701Commented:
try this
function countWeekDays($start,$end){
                  $d=0;if($end>$start){while($start<$end){if(date('w',$start)!=6 && date('w',$start)!=0){ $d++; }$start = strtotime('+1 day',$start);}}return $d ;
}
echo countWeekDays(strtotime("2009/02/17"),strtotime("2009/02/24"));

Open in new window

0
 
Michael701Commented:
Quick google showed this

http://weblog.thomassmart.com/2008/09/php-function-count-weekdays/
function countWeekDays($start,$end){
 $d=0;if($end>$start){while($start<$end){if(date('w',$start)!=6 && date('w',$start)!=0){ $d++; }$start = strtotime('+1 day',$start);}}return $d ;
}

Open in new window

0
 
DavidSingletonCommented:
If you have access to create mysql functions you could always do this:


And use that as a basis to get the date.

DROP FUNCTION IF EXISTS BizDateTimeDiff;
DELIMITER |
CREATE FUNCTION BizDateTimeDiff( d1 DATETIME, d2 DATETIME ) 
RETURNS CHAR(30)
DETERMINISTIC
BEGIN
  DECLARE dow1, dow2, days, wknddays INT;
  DECLARE tdiff CHAR(10);
  SET dow1 = DAYOFWEEK(d1);
  SET dow2 = DAYOFWEEK(d2);
  SET tdiff = TIMEDIFF( TIME(d2), TIME(d1) );
  SET days = DATEDIFF(d2,d1);
  SET wknddays = 2 * FLOOR( days / 7 ) +
                 IF( dow1 = 1 AND dow2 > 1, 1,                               
                     IF( dow1 = 7 AND dow2 = 1, 1,               
                         IF( dow1 > 1 AND dow1 > dow2, 2,       
                             IF( dow1 < 7 AND dow2 = 7, 1, 0 )   
                           )
                       )
                   );
  SET days = FLOOR(days - wkndDays) - IF( ASCII(tdiff) = 45, 1, 0 );
  SET tdiff = IF( ASCII(tdiff) = 45, TIMEDIFF( '24:00:00', SUBSTRING(tdiff,2)), TIMEDIFF( tdiff, '00:00:00' ));
  RETURN CONCAT( days, ' days ', tdiff );
END; 

Open in new window

0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
DavidSingletonCommented:
If you prefer using php i found this function at:
http://snippets.dzone.com/posts/show/807

A note about this rather large function is it also accounts for holidays, although you'll have to manually put them in because these are Australian holidays.
<?php
 
    /**
	 * Calculates the number of work days between 2 given times
	 *
	 * @see get_holidays()
	 *
	 * @param date $start_date First date
	 * @param date $end_date Second date
	 * @param bool $workdays_only Whether to count only work days (eg. Mon-Fri)
	 * @param bool $skip_holidays Whether to use the get_holidays() function to skip holiday days as well
	 * @return int $workday_counter Number of workdays between the 2 dates
	 */
function date_difference($start_date, $end_date, $workdays_only = false, $skip_holidays = false){
    $start_date = strtotime($start_date);
    $end_date = strtotime($end_date);
    $seconds_in_a_day = 86400;
    $sunday_val = "0";
    $saturday_val = "6";
    $workday_counter = 0;
    $holiday_array = array();
 
    $ptr_year = intval(date("Y", $start_date));
    $holiday_array[$ptr_year] = get_holidays(date("Y", $start_date));
 
    for($day_val = $start_date; $day_val <= $end_date; $day_val+=$seconds_in_a_day){
        $pointer_day = date("w", $day_val);
        if($workdays_only == true){
            if(($pointer_day != $sunday_val) AND ($pointer_day != $saturday_val)){
                if($skip_holidays == true){
                    if(intval(date("Y", $day_val))!=$ptr_year){
                        $ptr_year = intval(date("Y", $day_val));
                        $holiday_array[$ptr_year] = get_holidays(date("Y", $day_val));
                    }
                    if(!in_array($day_val, $holiday_array[date("Y", $day_val)])){
                        $workday_counter++;
                    }
                }else{
                    $workday_counter++;
                }
            }
        }else{
            if($skip_holidays == true){
                if(intval(date("Y", $day_val))!=$ptr_year){
                    $ptr_year = intval(date("Y", $day_val));
                    $holiday_array[$ptr_year] = get_holidays(date("Y", $day_val));
                }
                if(!in_array($day_val, $holiday_array[date("Y", $day_val)])){
                    $workday_counter++;
                }
            }else{
                $workday_counter++;
            }
        }
    }
    return $workday_counter;
}
 
    /**
	 * Takes a date in yyyy-mm-dd format and returns a PHP timestamp
	 *
	 * @param string $MySqlDate
	 * @return unknown
	 */
function get_timestamp($MySqlDate){
 
    $date_array = explode("-",$MySqlDate); // split the array
 
    $var_year = $date_array[0];
    $var_month = $date_array[1];
    $var_day = $date_array[2];
 
    $var_timestamp = mktime(0,0,0,$var_month,$var_day,$var_year);
    return($var_timestamp); // return it to the user
}
 
    /**
	 * Returns the date of the $ord $day of the $month.
	 * For example ordinal_day(3, 'Sun', 5, 2001) returns the
     * date of the 3rd Sunday of May (ie. Mother's Day).
     *
     * @author  heymeadows@yahoo.com
	 *
	 * @param int $ord
	 * @param string $day (must be 3 char abbrev, per date("D);)
	 * @param int $month
	 * @param int $year
	 * @return unknown
	 */
function ordinal_day($ord, $day, $month, $year) {
 
    $firstOfMonth = get_timestamp("$year-$month-01");
    $lastOfMonth  = $firstOfMonth + date("t", $firstOfMonth) * 86400;
    $dayOccurs = 0;
 
    for ($i = $firstOfMonth; $i < $lastOfMonth ; $i += 86400){
        if (date("D", $i) == $day){
            $dayOccurs++;
            if ($dayOccurs == $ord){
                $ordDay = $i;
            }
        }
    }
    return $ordDay;
}
 
function memorial_day($inc_year){
    for($date_stepper = intval(date("t", strtotime("$inc_year-05-01"))); $date_stepper >= 1; $date_stepper--){
        if(date("l", strtotime("$inc_year-05-$date_stepper"))=="Monday"){
            return strtotime("$inc_year-05-$date_stepper");
            break;
        }
    }
}
 
 
    /**
	 * Looks through a lists of defined holidays and tells you which
	 * one is coming up next.
	 *
	 * @author heymeadows@yahoo.com
	 *
	 * @param int $inc_year The year we are looking for holidays in
	 * @return array
	 */
function get_holidays($inc_year){
    //$year = date("Y");
    $year = $inc_year;
 
    $holidays[] = new Holiday("New Year's Day", get_timestamp("$year-1-1"));
    $holidays[] = new Holiday("Australia Day", get_timestamp("$year-1-26"));
    $holidays[] = new Holiday("Labour Day", ordinal_day(1, 'Mon', 3, $year));
    $holidays[] = new Holiday("Anzac Day", get_timestamp("$year-4-25"));
    //$holidays[] = new Holiday("St. Patrick's Day", get_timestamp("$year-3-17"));
    // TODO: $holidays[] = new Holiday("Good Friday", easter_date($year));
    $holidays[] = new Holiday("Easter", easter_date($year));
    // TODO: $holidays[] = new Holiday("Easter Monday", easter_date($year));
    $holidays[] = new Holiday("Foundation Day", ordinal_day(1, 'Mon', 6, $year));
    $holidays[] = new Holiday("Queen's Birthday", ordinal_day(1, 'Mon', 10, $year));
    //$holidays[] = new Holiday("Memorial Day", memorial_day($year));
    //$holidays[] = new Holiday("Mother's Day", ordinal_day(2, 'Sun', 5, $year));
    //$holidays[] = new Holiday("Father's Day", ordinal_day(3, 'Sun', 6, $year));
    //$holidays[] = new Holiday("Independence Day", get_timestamp("$year-7-4"));
    //$holidays[] = new Holiday("Labor Day", ordinal_day(1, 'Mon', 9, $year));
    $holidays[] = new Holiday("Christmas", get_timestamp("$year-12-25"));
    $holidays[] = new Holiday("Boxing Day", get_timestamp("$year-12-26"));
 
    $numHolidays = count($holidays) - 1;
    $out_array = array();
 
    for ($i = 0; $i < $numHolidays; $i++){
        $out_array[] = $holidays[$i]->date;
    }
    unset($holidays);
    return $out_array;
}
 
class Holiday{
    //var $name;
    //var $date;
    public $name;
    public $date;
 
    // Contructor to define the details of each holiday as it is created.
    function holiday($name, $date){
        $this->name   = $name;   // Official name of holiday
        $this->date   = $date;   // UNIX timestamp of date
    }
}
 
?>

Open in new window

0
 
thedeal56Author Commented:
Thanks for the responses! I started off by attempting the solution that Michael701 provided.  Here's what I did:

function countWeekDays($start,$end){
                  $d=0;if($end>$start){while($start<$end){if(date('w',$start)!=6 && date('w',$start)!=0){ $d++; }$start = strtotime('+1 day',$start);}}return $d ;
}
echo countWeekDays("2009/02/17","2009/02/24");

The result that this prints out is "1".  What am I doing wrong?
0
 
thedeal56Author Commented:
That did it, thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.