Differences in weeks between PHP and MySQL

Question

I have the following query.

SELECT COUNT(*), WEEK(date), YEAR(date) FROM myTable GROUP ON YEAR(date), WEEK(date)

Say it produces the following results

32 33 2012
43 34 2012
39 35 2012
17 36 2012

I now want to get all 39 records in week 35 of 2012. I do not, however, wish to use WEEK(date)=35 AND YEAR(date)=2012 in my WHERE clause as it does not utilize indexes. Instead, I wish to find the boundaries and use conditionals. I also do not want to use BETWEEN as rounding errors might occur.

I therefore try the following thinking all is good, but do not get 39 records. Obviously MySQL and PHP deal differently with weeks. I see that MySQL WEEK() utilizes mode 0, 2, 4, and 6 that all return a week that starts with Sunday. Ideally, I would have the one that is most commonly used by people, the most important thing is that it is the same as provided by DateTime. How would I do so? Thank you

$w=35;$y=2012;   //Given
$w=sprintf('%02d',$w);    //Make sure it is two digits
$date = new DateTime($y.'W'.$w);
$d1=$date->format('Y-m-d');
$date->add(new DateInterval('P1W'));
$d2=$date->format('Y-m-d');
$sql='SELECT * FROM myTable WHERE date >= ? AND date < ?';

Answer 1

You are on the right track with regards to how MySQL works, having various modes for week-related functions that can produce different results. http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_week

It is my understanding the MySQL mode which is equivalent to PHP's date logic is mode 3, which is the ISO week date standard http://en.wikipedia.org/wiki/ISO_week_date

This has weeks starting on Mondays and weeks numbered 1-53.

So you would need to use WEEK(date_field, 3) to get PHP compatible values.

As an alternate approach, one thing I have found handy in cases where you need the ability to flexibly query on different date ranges or aggregations (Q1- Q4, H1 - H2, etc.), or where you might need to utilize different weeks than PHP supports is to use a date helper table in MySQL (similar to what one may use as a date dimension table in a data warehouse, if you are familiar with that). It can give you a convenient table to join against in order to look up date ranges. Something like this should work:

http://databobjr.blogspot.com/2012/06/create-date-dimension-table-in-mysql.html