MySQL Forums
Forum List  »  Partitioning

Partition by year and month
Posted by: IGGt .
Date: December 07, 2010 08:35AM

I am looking to create a database containing our monthly data (at the moment it gets archived away and restored when needed), and I think that a Partitioned table would be ideal, as I can add the data for every month for the past xx years, and still have a searchable table for querying (hopefully). But, I can't get the create query to work how I expected.

What I have is:
CREATE TABLE archive (c1 DATE)
PARTITION BY RANGE( YEAR(c1) )
SUBPARTITION BY RANGE( MONTH(c1) )
SUBPARTITIONS 12 (
PARTITION p2007 VALUES LESS THAN (2008),
PARTITION p2008 VALUES LESS THAN (2009),
PARTITION p2009 VALUES LESS THAN (2010),
PARTITION p2010 VALUES LESS THAN MAXVALUE
);

The idea being I would have a partition for each year, and a subpartition for each month.

It seems to be complaining at the "RANGE( MONTH(c1) )" stage

Options: ReplyQuote


Subject
Views
Written By
Posted
Partition by year and month
30344
December 07, 2010 08:35AM
10195
December 08, 2010 10:01PM
9125
December 13, 2010 03:07AM
8133
December 13, 2010 08:22AM
8725
December 13, 2010 08:25AM


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.