I'd like to have an array of yearweeks between two given dates, using MySQL / PHP.
Until now, I used to do it simple as the dates couldn't be on different years.
// $yw_min = yearweek of the min date
// $yw_max = yearweek of the max date
$yearweeks = array();
for ($i = $yw_min; $i <= $yw_max; $i++) {
$yearweeks[$i] = "W " . substr($i, 4, 6);
}
/*
yearweeks : Array (
[201501] => W 01
[201502] => W 02
[201503] => W 03
[201504] => W 04
[201505] => W 05
)
*/
These doesn't work anymore since my dates can be on différent years.
Having a database with all dates for the 10 years to come is not an option.
To increment year when week > 52 is not conceivable either since I don't know if the year contains 51, 52 or 53 weeks.
Edit: here is all the background of the story
I have to build a week data-based graph. Originally, I had year and quarter of year as only "date".
I came on on a function to give me yearweeks of the (year + quarter) :
function getYearweeks($year, $quarter) {
$min = ($quarter - 1) * 3 +1;
$max = $quarter * 3 + 1;
$dmin = $year . "-" . (($min < 10)?"0" . $min:$min) . "-01";
$dmax = (($max > 12)?($year + 1) . "-01-01":$year . "-" . (($max < 10)?"0" . $max:$max) . "-01");
$q = "SELECT YEARWEEK('" . $dmin . "',1) AS yw_min,
YEARWEEK(DATE_SUB('" . $dmax . "', INTERVAL 1 DAY),1) AS yw_max";
$r = mysql_query($q) or die (mysql_error()."<br />".$q."<br />");
$d = mysql_fetch_assoc($r);
$yw_min = $d['yw_min'];
$yw_max = $d['yw_max'];
$yearweeks = array();
for ($i = $yw_min; $i <= $yw_max; $i++) {
$yearweeks[$i] = "W " . substr($i, 4, 6);
}
return $yearweeks;
}
Problem is that now, the client wants to "slide" ; I added a parameter "interval" on my function which is a number of week to add (or sub) to the original date (year + quarter) :
function getYearweeks($year, $quarter, $interval=0) {
$min = ($quarter - 1) * 3 +1;
$max = $quarter * 3 + 1;
$dmin = $year . "-" . (($min < 10)?"0" . $min:$min) . "-01";
$dmax = (($max > 12)?($year + 1) . "-01-01":$year . "-" . (($max < 10)?"0" . $max:$max) . "-01");
$q = "SELECT YEARWEEK(DATE_ADD('" . $dmin . "', INTERVAL " . $interval . " WEEK),1) AS yw_min,
YEARWEEK(DATE_SUB(DATE_ADD('" . $dmax . "', INTERVAL " . $interval . " WEEK), INTERVAL 1 DAY),1) AS yw_max";
$r = mysql_query($q) or die (mysql_error()."<br />".$q."<br />");
$d = mysql_fetch_assoc($r);
$yw_min = $d['yw_min'];
$yw_max = $d['yw_max'];
// mess up when we are between two different years
$yearweeks = array();
for ($i = $yw_min; $i <= $yw_max; $i++) {
$yearweeks[$i] = "W " . substr($i, 4, 6);
}
return $yearweeks;
}