How many weeks are inside of two dates

I have starting dates and ending dates in my database (MySQL). How can I get the answer, how many weeks开发者_运维知识库(or days) are inside of those 2 dates? (mysql or php)

For example I have this kind of database:

Started and | will_end

2009-12-17 | 2009-12-24

2009-12-12 | 2009-12-26

...

Update to the question:

How to use DATEDIFF? How can I make this to work? or should I use DATEDIFF completly differently?

SELECT DATEDIFF('Started ','will_end') AS 'Duration' FROM my_table WHERE id = '110';

---

If the two columns $d1 and $d2 store unix timestamp obtained from time() then this simple line suffices:

$diffweek = abs($d1 - $d2) / 604800; 

Otherwise if the columns are of DATETIME type, then:

$diffweek = abs(strtotime($d1) - strtotime($d2)) / 604800; 

p/s: 604800 is the number of seconds in a week (60 * 60 * 24 * 7)

p/s2: you might want to intval($diffweek) or round($diffweek)

---

Calculating the number of days and dividing by seven won't give you the number of weeks between the two dates. Instead it will return the result of division by 7 that doesn't always correspond to the number of weeks between the two dates when thinking in terms of the number of weeks in the ISO calculation.

For example, given start_date = "2010-12-26" and end_date = "2011-01-25" you will be going through W51,52,01,02,03,04 and those are 6 weeks as per ISO, but if you simply calculate the difference and divide by 7, you'll get 5.

The issue appears when the start date and end date belong to different years.

The best way to do the calculation is to get the last week number of the start_date year and it should refer to the December, 28.

function weeks($ladate2,$ladate3) {
    $start_week= date("W",strtotime($ladate2));
    $end_week= date("W",strtotime($ladate3));
    $number_of_weeks= $end_week - $start_week;

    $weeks=array();
    $weeks[]=$start_week;
    $increment_date=$ladate2;
    $i="1";

    if ($number_of_weeks<0){
        $start_year=date("Y",strtotime($ladate2));
        $last_week_of_year= date("W",strtotime("$start_year-12-28"));
        $number_of_weeks=($last_week_of_year-$start_week)+$end_week;
    }

    while ($i<=$number_of_weeks)
    {
        $increment_date=date("Y-m-d", strtotime($ladate2. " +$i week"));
        $weeks[]=date("W",strtotime($increment_date));

        $i=$i+1;
    }

    return $weeks;
}

function diff_weeks($ladate2,$ladate3) {
    $weeks=weeks($ladate2,$ladate3);
    $diff_weeks=count($weeks);

    return $diff_weeks;
}

Best regards, Manikam

---

MySQL has datediff which returns the difference in days between two dates, since MySQL 4.1.1.

Do note that, as per the manual, DATEDIFF(expr1,expr2) returns expr1 – expr2 expressed as a value in days from one date to the other. expr1 and expr2 are date or date-and-time expressions. Only the date parts of the values are used in the calculation.

---

You can use the TO_DAYS function on each date and subtract the two to calculate the difference in days.

---

DATEDIFF

Find the days and divide by 7

---

<?php
  $dayDif    = date('z',strtotime('2009-12-17)') - date('z',strtotime('2009-12-24)');
  $numWeeks  = $dayDif / 7;
?>

The z option for php's date function gives you the day of the year (0 - 365). By subtracting the two values you find how many days between dates. Then factor by seven for the number of weeks.

Read this page closely, the date() function is rich. http://php.net/manual/en/function.date.php