> I did it using :
> PARTITION BY RANGE (cast(DATE_FORMAT(date, '%Y%V') as unsigned))
Right, this does the same thing as YEARWEEK():
mysql> SET @d = NOW();
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @d;
+---------------------+
| @d |
+---------------------+
| 2006-10-04 08:52:55 |
+---------------------+
1 row in set (0.00 sec)
mysql> SELECT DATE_FORMAT(@d, '%Y%V') AS a, YEARWEEK(@d) AS b;
+--------+--------+
| a | b |
+--------+--------+
| 200640 | 200640 |
+--------+--------+
1 row in set (0.00 sec)
I imagine that ABS() might work as well as CAST (and I'm not sure that either is even necessary) - for now. However, complex expressions such as Robert's might not be supported once Bug #18198
http://bugs.mysql.com/bug.php?id=18198 is fixed.
The point that Mikael was trying to make is that functions such as WEEK(), QUARTER(), and MONTH() yield repeating values. For example:
mysql> SELECT WEEK('2006-10-02') AS a,
-> WEEK('2005-10-03') AS b,
-> WEEK('2007-10-07') AS c;
+------+------+------+
| a | b | c |
+------+------+------+
| 40 | 40 | 40 |
+------+------+------+
1 row in set (0.00 sec)
Such functions can't (easily) be optimised, because there's no guaranteed way to map an arbitrary input interval (a, b) to a unique output interval (F(a), F(b)) where F is a function such as one of these.
Jon Stephens
MySQL Documentation Team @ Oracle
MySQL Dev Zone
MySQL Server Documentation
Oracle