Reputation: 337

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';

Upvotes: 3

Answers (6)

manikam

Reputation: 21

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

Upvotes: 2

Lukman

Reputation: 19164

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)

Upvotes: 2

Adriano Varoli Piazza

Reputation: 7429

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.

Upvotes: 1

Christopher Altman

Reputation: 4896

<?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

Upvotes: -2