Differences between weeks between PHP and MySQL

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;   //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 < ?';
+5
source share
1 answer

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

, MySQL, PHP, - 3, http://en.wikipedia.org/wiki/ISO_week_date

, 1-53.

, WEEK(date_field, 3) , PHP.

, , , (Q1-Q4, H1-H2 ..) , PHP, MySQL ( , , ). , . - :

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

+11

All Articles