• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 478
  • Last Modified:

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.
0
thedeal56
Asked:
thedeal56
  • 2
  • 2
  • 2
1 Solution
 
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
 
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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
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
 
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
 
thedeal56Author Commented:
That did it, thanks.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now