MySQL Forums
Forum List  »  Partitioning

Re: Partition by Date Column
Posted by: Jonathan Stephens
Date: October 03, 2006 09:04PM

> 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

Options: ReplyQuote


Subject
Views
Written By
Posted
17499
July 05, 2006 10:23AM
5796
July 24, 2006 09:01AM
5196
September 04, 2006 09:56PM
4542
September 05, 2006 05:07PM
6051
September 06, 2006 01:34AM
4097
October 03, 2006 12:06PM
Re: Partition by Date Column
5121
October 03, 2006 09:04PM
3981
October 25, 2006 08:45AM
3899
October 26, 2006 11:24PM
3770
January 28, 2009 02:08AM
3806
March 19, 2009 01:35AM
3591
April 06, 2009 08:15AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.