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
17745
July 05, 2006 10:23AM
5867
July 24, 2006 09:01AM
5300
September 04, 2006 09:56PM
4638
September 05, 2006 05:07PM
6146
September 06, 2006 01:34AM
4192
October 03, 2006 12:06PM
Re: Partition by Date Column
5220
October 03, 2006 09:04PM
4076
October 25, 2006 08:45AM
4016
October 26, 2006 11:24PM
3847
January 28, 2009 02:08AM
3887
March 19, 2009 01:35AM
3674
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.