Week Number Between Dates

[MoFish]

Hi,

 

I'm trying to associate a week number for a date I receive from my database.

 

The week number is calculated between the last Friday of the previous month and the last Friday of the current month for that particular date. I would expect the week number to be 1,2,3 or in some cases maybe a 4 week month.

 

I tried to use DatePeroid without much success, I've never used it before but feel im using it incorrectly as something is not quite right.

 

Could anyone help?

 

Regards,

 

MoFish

// try to find the week number
foreach(new DatePeriod($rstart, new DateInterval('P1D'), $rend) as $date) {
   $item->SALE_WEEK_NUMBER = "Week".$date->format('W');   
}  

The code below calculates the date ranges from the database date.

        // foreach
        foreach ($return as $item){
                            
            // month range start
            $rs = new DateTime($item->SALE_DATE, $time_zone);
            $rstart = $rs->modify('last friday of previous month');
            echo "<br/><br/> Range Start: " . $rs->format('Y-m-d');
            
            // month range date
            $re = new DateTime($item->SALE_DATE, $time_zone);
            $rend = $re->modify('last friday of this month');
            echo "<br/> Range End: " . $re->format('Y-m-d');

            // try to find the week number
            foreach(new DatePeriod($rstart, new DateInterval('P1D'), $rend) as $date) {
                $item->SALE_WEEK_NUMBER = "Week".$date->format('W');    
            }            
            
        }

[requinix]

I'm not really sure what you're trying to do with DatePeriod...

 

Anyway, you can't use date("W") because that has weeks starting on Monday. It's also for the entire year, not just a month.

I don't think there's anything built-in to do this already so you'll have to come up with it on your own.

 

1. Find the last Friday of the previous month

2. Find the last Friday of the current month

3. Pick a starting Friday based on those

4. Calculate the number of days between that Friday and the current date

function week_number(DateTime $date) {
	// previous month's last friday
	$previous = new DateTime($date->format("Y-m-d") . " last Friday of last month");
	// this month's last friday
	$current = new DateTime($date->format("Y-m-d") . " last Friday of this month");
	
	// pick a starting friday
	if ($date >= $current) {
		$start = $current;
	} else {
		$start = $previous;
	}

	$diff = $start->diff($date); // $diff = $date - $start
	return floor($diff->d / 7) + 1;
}

And a quick test:

foreach (["01", "02"] as $month) {
	$start = new DateTime("2017-{$month}-01");

	echo $start->format("F Y"), "\n";
	echo "Su Mo Tu We Th Fr Sa\n";
	echo "--------------------";
	if ($start->format("w") > 0) {
		echo "\n", str_repeat("   ", $start->format("w"));
	}
	foreach (new DatePeriod($start, new DateInterval("P1D"), $start->format("t") - 1) as $day) {
		if ($day->format("w") == 0) {
			echo "\n";
		}
		echo " ", week_number($day), " ";
	}
	echo "\n\n";
}

January 2017
Su Mo Tu We Th Fr Sa
--------------------
 1  1  1  1  1  2  2
 2  2  2  2  2  3  3
 3  3  3  3  3  4  4
 4  4  4  4  4  1  1
 1  1  1

February 2017
Su Mo Tu We Th Fr Sa
--------------------
          1  1  2  2
 2  2  2  2  2  3  3
 3  3  3  3  3  4  4
 4  4  4  4  4  1  1
 1  1  1

[MoFish]

Hi Requinix,

 

Thanks for taking the time to respond.

 

I have tried your example - but am getting the following when trying it with my code from value from the DB.

 

Argument 1 passed to sale_model::week_number() must be an instance of DateTime

 

The value is 2017-02-17 00:00:00 from the database - should this be amended before going into the function?

 

Thanks,

 

MoFish

// return
$return = $this->query("select * from SALE;", true);
		
// foreach
foreach ($return as $item){
    echo $item->SALE_DATE; //2017-02-17 00:00:00 
    $week = $this->week_number($item->SALE_DATE);
    $item->week = $week;
}

// print_r ($item);

[Barand]

The algorithm seems to break down in June

June 2017
Su Mo Tu We Th Fr Sa
--------------------
             1  2  2 
 2  2  2  2  2  3  3 
 3  3  3  3  3  4  4 
 4  4  4  4  4  5  5 
 5  1  1  1  1  1 

[Barand]

I think you need $diff->days and not $diff->d

[MoFish]

Thanks Barand,

 

I've not been able to use the function successfully due to the information I'm passing in being incorrect. I'm not sure how best to format this.

 

DB value: // 2017-02-17 00:00:00

 

Argument 1 passed to sale_model::week_number() must be an instance of DateTime

Edited February 21, 2017 by MoFish

[ginerjm]

I'm guessing that you are using the db value that is returned by the query which would be a type string. Convert that to a type datetime value before using it.

[Barand]

You need to convert the database datetime field to a PHP DateTime object

$dtobj = new DateTime($dbdatetime);

[requinix]

I think you need $diff->days and not $diff->d

Huh. You'd think "Number of days" would be the right one... I guess "total number of days" is the better choice :-\

[Barand]

->d gives the number of days left after the months have been calculated

 

->days gives the total days difference

 

EG

$startdate = new DateTime('2017-01-01');
$enddate = new dateTime(); // today

// output difference in months and days
$months = $enddate->diff($startdate)->m;
$days   = $enddate->diff($startdate)->d;
echo "$months months $days days<br>";                 // 1 months 20 days

// output difference in days only
$days = $enddate->diff($startdate)->days;             // 51 days
echo "$days days";

[requinix]

I think I used 'd' accidentally, but regardless my thought process was that the number of days would be less than the length of a month... but there will be cases where that's not true, even though a handful of conditions have to be met for it. Good catch.

[Barand]

I chose June for a test month as it was the first one I found with 5 Fridays.

[MoFish]

Thanks, I think I have got the week calculation bit working after some messing around.

 

I am trying to change my array slightly to 'group' the results into week numbers but am hitting some road blocks. I seem to get one week in the correct one, but not all of them

 

I currently have:

Array
(
    [0] => stdClass Object
        (
            [SALE_ID] => 1
            [SALE_DATE] => 2017-01-01
            [SALE_WEEK] => 1
        )

    [1] => stdClass Object
        (
            [SALE_ID] => 1
            [SALE_DATE] => 2017-02-15
            [SALE_WEEK] => 4
        )

    [2] => stdClass Object
        (
            [SALE_ID] => 1
            [SALE_DATE] => 2017-03-25
            [SALE_WEEK] => 1
        )

    [3] => stdClass Object
        (
            [SALE_ID] => 1
            [SALE_DATE] => 2017-04-10
            [SALE_WEEK] => 3
        )

)

And would like to have something like the below - whereby I can loop out all the records based on the week.

Array
(
    [week1] => stdClass Object
    (
        [1] => stdClass Object
        (
            [SALE_ID] => 1
            [SALE_DATE] => 2017-01-01
            [SALE_WEEK] => 1
        )
        [2] => stdClass Object
        (
            [SALE_ID] => 1
            [SALE_DATE] => 2017-03-25
            [SALE_WEEK] => 1
        )
    )
    [week3] => stdClass Object
    (
        [1] => stdClass Object
        (
            [SALE_ID] => 1
            [SALE_DATE] => 2017-04-10
            [SALE_WEEK] => 3
        )
    )
    [week4] => stdClass Object
    (
        [1] => stdClass Object
        (
            [SALE_ID] => 1
            [SALE_DATE] => 2017-02-15
            [SALE_WEEK] => 4
        )
    )
)

At the moment I have:

		$results = array(
		  	array("SALE_ID"=>"1","SALE_DATE"=>"2017-01-01"),
		  	array("SALE_ID"=>"1","SALE_DATE"=>"2017-02-15"),
		  	array("SALE_ID"=>"1","SALE_DATE"=>"2017-03-25"),
		  	array("SALE_ID"=>"1","SALE_DATE"=>"2017-04-10")
		);
		
		$obj = json_decode (json_encode ($results), FALSE);
		
		foreach ($obj as $r){
			$date = new DateTime($r->SALE_DATE);
			$r->SALE_WEEK = $this->sale_model->week_calc($date);
		}

Thanks,

 

MoFish

[Barand]

try

$results = array(
              array("SALE_ID"=>"1","SALE_DATE"=>"2017-01-01"),
              array("SALE_ID"=>"2","SALE_DATE"=>"2017-02-15"),
              array("SALE_ID"=>"3","SALE_DATE"=>"2017-03-25"),
              array("SALE_ID"=>"4","SALE_DATE"=>"2017-04-10"),
              array("SALE_ID"=>"5","SALE_DATE"=>"2017-01-08"),
              array("SALE_ID"=>"6","SALE_DATE"=>"2017-02-23"),
              array("SALE_ID"=>"7","SALE_DATE"=>"2017-03-15"),
              array("SALE_ID"=>"8","SALE_DATE"=>"2017-04-09")
        );
        
$results_by_week = [];

foreach ($results as $sale) {
    $w = week_number($sale['SALE_DATE']);
    $results_by_week[$w][] = (object)$sale;
}       

ksort($results_by_week);

[MoFish]

Hi Barand,

 

Thats exactly what I needed.

 

Thank you very much.

 

MoFish

[MoFish]

Hi,

 

I am looking to alter this slightly and am not sure how best to go around it. What I am now trying to do is:

• Set a start date e.g first day of January (2017-01-01)
• Set a end date e.g last day of December (2017-12-31)
• Find the number of weeks between the two date ranges (2017-01-01, 2017-12-31) which returns 52 weeks.
• And the tricky bit - for each of these 52 weeks - find the weeks startdate (Monday this week) and enddate (Friday this week).

I have got the first three steps working using the function below, but am a little unsure on how best to find out the start date and end dates of these particular 52 weeks.

 

I am maybe over thinking it, but i cannot understand gow best to tackle the next steps - as am unsure on how to find out a single date in each week.

 

Any help much appriciated.

 

MoFish

function datediffInWeeks($start, $end)
{
    if($start > $end) return datediffInWeeks($end, $start);
    $sd = DateTime::createFromFormat('Y-m-d', $start);
    $ed = DateTime::createFromFormat('Y-m-d', $end);
    return floor($sd->diff($ed)->days/7);

    // foreach of these weeks
       // find the start date 'Monday this week'
       // find the end date 'Friday this week'
    // return an array
}

var_dump(datediffInWeeks('2017-01-01', '2017-12-31')); // 52

[Barand]

Probably not what you want but there may be something in here that might help

$dt = new DateTime('2017-01-01');
$dt->modify('next monday');

$di7 = new DateInterval('P7D');
$dp = new DatePeriod($dt, $di7, 52);

echo "<pre>\nWeek Monday      Friday      \n";
$i=0;
foreach ($dp as $d) {
    printf("%3d  %-12s%-12s\n", ++$i, $d->format('Y-m-d'), $d->modify('+4 days')->format('Y-m-d'));
}