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
17589
July 05, 2006 10:23AM
5828
July 24, 2006 09:01AM
5239
September 04, 2006 09:56PM
4573
September 05, 2006 05:07PM
6091
September 06, 2006 01:34AM
4139
October 03, 2006 12:06PM
Re: Partition by Date Column
5160
October 03, 2006 09:04PM
4023
October 25, 2006 08:45AM
3933
October 26, 2006 11:24PM
3800
January 28, 2009 02:08AM
3838
March 19, 2009 01:35AM
3628
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.