DateTime: Converting 'yW' to earliest and latest possible dates, then BACK TO 'yW'?

Question

The Issue:

We have some products which use a "YYWW" (date('yW')) format to identify date of creation.
Sometimes we don't even get the week .. all we have to work with is the year value.

I am currently using DateTime->setISODate() to get dates out of the year and week values.
Since the turn of a new year usually falls in the middle of a calendar week, our minimum-date is typically in the prior year when considering week-One.
This isn't much of a problem in and of itself, bet when trying to convert back to the original date code string, I'm getting the wrong year value.

Goal:

Our goal is to

1. store a given YYWW value to the DB as two values, their earliest and latest possible Dates.
2. then, from those min/max dates, accurately rebuild the original year/week string when needed for views.

Examples: 'Code' to 'date': OK

• Code "09" becomes min 2008-12-29 / max 2009-12-27
• Code "0901" becomes min 2008-12-29 / max 2009-01-04
• Code "0922" becomes min 2009-05-25 / max 2009-05-31

Examples: 'date' to 'Code': FAILED

• Date 2009-12-27 = "0952" ... success
• Date 2009-05-31 = "0922" ... success
• Date 2009-05-25 = "0922" ... success
• Date 2009-01-04 = "0901" ... success
• Date 2008-12-29 = "0801" ... FAIL: expected "0901"

Tried:

### Simplified for brevity ###

$dateOfMfg = '0901';

$dc  = aCustomValidatorAndStuff($dateOfMfg); // returns array('year' => 2009, 'week' => 1)

$dateTimeMin = new DateTime();
$dateTimeMax = new DateTime();

$result = array(
    'min' => $dateTimeMin->setISODate($dc['year'], $dc['week'], 1),
    'max' => $dateTimeMax->setISODate($dc['year'], $dc['week'], 7),
);

echo '<pre>Result:<br />',var_dump($result),'</pre>';
echo 'Min: ',$result['min']->format('yW'),'<br />';  // "0801" ... Bad
echo 'Min: ',$result['max']->format('yW'),'<br />';  // "0901" ... Good

Summary:

Is there a native way to get the appropriate year value for that "min" year?
What other suggestions come to mind?

Answer 1

I think storing a calendar table in the database makes the most sense here. A calendar table can greatly simplify your code. Queries written against a calendar table are often obviously correct. There's a lot of value in that.

One way to answer your failed question with a calendar table is to return the full set of dates.

select cal_date
from calendar
where iso_year = (select iso_year from calendar where cal_date = '2008-12-29')
  and iso_week = (select iso_week from calendar where cal_date = '2008-12-29')
order by cal_date;

cal_date
--
2008-12-29
2008-12-30
2008-12-31
2009-01-01
2009-01-02
2009-01-03
2009-01-04

I think that statement is easier to understand, but a JOIN often performs better.

select c2.cal_date
from calendar c1
inner join calendar c2 
        on c1.iso_year = c2.iso_year
       and c1.iso_week = c2.iso_week
where c1.cal_date ='2008-12-29'
order by c2.cal_date;

Something to think about

Code "09" becomes min 2008-12-29 / max 2009-12-27

The ISO year 2009 starts on 2008-12-29, but it doesn't end on 2009-12-27. It ends on 2010-01-03;