/SQL/MySQL PARTITION BY month automatically

MySQL PARTITION BY month automatically

ALTER TABLE t1 PARTITION BY RANGE(transaction_date)(
PARTITION JAN VALUES LESS THAN (UNIX_TIMESTAMP('2013-02-01')),
PARTITION FEB VALUES LESS THAN (UNIX_TIMESTAMP('2013-03-01')),
PARTITION MAR VALUES LESS THAN (UNIX_TIMESTAMP('2013-04-01')),
PARTITION APR VALUES LESS THAN (UNIX_TIMESTAMP('2013-05-01')),
PARTITION MAY VALUES LESS THAN (UNIX_TIMESTAMP('2013-06-01')),
PARTITION JUN VALUES LESS THAN (UNIX_TIMESTAMP('2013-07-01')),
PARTITION JUL VALUES LESS THAN (UNIX_TIMESTAMP('2013-08-01')),
PARTITION AUG VALUES LESS THAN (UNIX_TIMESTAMP('2013-09-01')),
PARTITION SEP VALUES LESS THAN (UNIX_TIMESTAMP('2013-10-01')),
PARTITION `OCT` VALUES LESS THAN (UNIX_TIMESTAMP('2013-11-01')),
PARTITION NOV VALUES LESS THAN (UNIX_TIMESTAMP('2013-12-01')),
PARTITION `DEC` VALUES LESS THAN (UNIX_TIMESTAMP('2014-01-01'))
);

Source: stackoverflow.com

MySQL PARTITION BY month automatically

ALTER TABLE t1 
PARTITION BY RANGE( MONTH(FROM_UNIXTIME(transaction_date) )
SUBPARTITION BY HASH( DAY(FROM_UNIXTIME(transaction_date)) )
SUBPARTITIONS 31 (
    PARTITION p0 VALUES LESS THAN (2),
    PARTITION p1 VALUES LESS THAN (3),
    PARTITION p2 VALUES LESS THAN (4),
    PARTITION p3 VALUES LESS THAN (5),
    PARTITION p4 VALUES LESS THAN (6),
    PARTITION p5 VALUES LESS THAN (7),
    PARTITION p6 VALUES LESS THAN (8),
    PARTITION p7 VALUES LESS THAN (9),
    PARTITION p8 VALUES LESS THAN (10),
    PARTITION p9 VALUES LESS THAN (11),
    PARTITION p10 VALUES LESS THAN (12),
    PARTITION p11 VALUES LESS THAN MAXVALUE
);

Source: stackoverflow.com

Similar codes

3 votes349 views1 answer
Mysql load sql from file
SQL
3 votes361 views2 answers
Find string in stored procedure sql server
SQL
0 vote348 views1 answer
Insert array into mysql column
SQL
3 votes359 views2 answers
Mysql config user password
SQL
1 vote334 views1 answer
Import in mysql without a software
SQL
-1 vote345 views2 answers
On update cascade
SQL
0 vote374 views1 answer
Get last three characters in mysql column
SQL
-1 vote363 views1 answer
Can pandas fetch data from sql
SQL
0 vote346 views1 answer
Postgresql import a database of gzip
SQL
0 vote348 views1 answer
Mysql coonect sample code
SQL