I have the following query.
SELECT COUNT(*), WEEK(date), YEAR(date) FROM myTable GROUP ON YEAR(date), WEEK(date)
Let's say it gives the following results
32 33 2012
43 34 2012
39 35 2012
17 36 2012
Now I want to get all 39 entries at week 35 of 2012. However, I do not want to use WEEK(date)=35 AND YEAR(date)=2012WHERE in my clause, since it does not use indexes. Instead, I want to find boundaries and use conventions. I also do not want to use BETWEEN as rounding errors may occur.
Therefore, I try to think that everything is fine, but not get 39 records. Obviously, MySQL and PHP work differently with weeks. I see that MySQL WEEK () uses modes 0, 2, 4, and 6, which return a week that starts on Sunday. Ideally, I would use the one that is most often used by people, most importantly, that it is the same as in DateTime. How should I do it? Thanks you
$w=35;$y=2012;
$w=sprintf('%02d',$w);
$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 < ?';
source
share