How Many Days have occured during a Given Timespan

Question

If we have date range such As:

1st October 2009 - 20th October 2009

Can we calculate how many Mondays have occured during

The answer to this would be (3)

  M   T   W   T   F   S   S
             *1   2   3   4
  5   6   7   8   9  10  11
 12  13  14  15  16  17  18
 19 *20  21  22  23  24  25
 26  27  28  29  30  31

Can we do this in MySQL, if not can it be done in PHP or would i have to create a function

Answer 1

function daysBetween($from, $to, $round = true) {
    // assume from and two can be valid date() values or strings
    $from = strtotime($from);
    $to = strtotime($to);
    $diff = $to - $from;
    $days = $diff / 86400;
    return ($round) ? floor($days) : round($days, 2);
}

Answer 2

You can start with something like:

select datecol1, TIMESTAMPDIFF(DAY,datecol1,datecol2) as numDays from table;

Then in PHP, you can pretty directly figure out how many mondays follow d1, depending on what day of the week d1 is (you can use date() and strtotime() to figure this out), and the value of numDays in the result set.

EDIT: Untested code, but the general idea should work:

function daysUntilMonday($date){
    $dayOfWeek = date('w',strtotime($date));  //a number from 0-6, sunday is 0
    $daysTillMonday = (8 - $dayOfWeek) % 7; //modulus is your friend.
    return $daysTillMonday;    
}

function countMondays($start,$numDays){
    $numDays = $numDays - daysUntilMonday($start);
    return floor($numDays/7)+1;
}

Could be off by one, but should be generally the right approach.

HTH

Answer 3

not sure if its possible with MySQL . PHP is ur friend there.. several ways to go about it .. get start timestamp and end timestamp by using strtotime probably .. then run a for loop or something from start timestamp to end timestamp by adding 86400 and on each increment check the date("D" , $mkt) == 'Mon' and see if its Monday and then increment ur counter. have given u the rough idea.. u can figure out the code from there.

Answer 4

sdt = start date of interval, edt = end date of interval. Change '2' to reflect the day of the week you want to count.

CREATE FUNCTION NMONDAYS( sdt DATETIME,  edt DATETIME ) 
RETURNS INT DETERMINISTIC RETURN TIMESTAMPDIFF( WEEK, sdt, edt ) 
+ IF( DAYOFWEEK( edt ) >= 2 AND DAYOFWEEK( edt ) < DAYOFWEEK( sdt ),1,0)

Answer 5

In sql:

SELECT DATEDIFF(
        ADDDATE('20091001', 9 - CASE WHEN DAYOFWEEK('20091001') = 1 THEN 8
                                     WHEN DAYOFWEEK('20091001') = 2 THEN 9
                                     ELSE DAYOFWEEK('20091001') 
                                END), -- first monday after date1
        ADDDATE('20091020', 2 - CASE WHEN DAYOFWEEK('20091020') = 1 THEN 8
                                     ELSE DAYOFWEEK('20091020') 
                                END)  -- first monday before date2
               )/7 + 1 -- divide the difference by 7 gives number of weeks
                       -- add 1 (nb points = nb intervals + 1)
              ) NbMonday

Answer 6

Or this:

// Pass $date_1, $date_2 as timestamps, $dow as 3-letter abbrev. (e.g. 'Mon').
// Dates can be low to high or high to low.

function dow_between($date_1, $date_2, $dow)
{
    if ($date_2 < $date_1) {
        list($date_2, $date_1) = array($date_1, $date_2);
    }
    for ($num_dow = -1; $date_1 <= $date_2; $num_dow++) {
        $date_1 = strtotime('next ' . $dow, $date_1);
    }
    return $num_dow;
}

Edited to handle dates in either order.