/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

25 votes173 views1 answer
Mysql grant all privileges to a user
SQL
5 votes141 views1 answer
Copy table from one database to another mysql
SQL
0 vote180 views1 answer
Mysql multiple group by
SQL
0 vote200 views1 answer
Amount of entries in a table psql
SQL
0 vote181 views1 answer
Mysql url data type
SQL
0 vote184 views1 answer
Laravel eloquent get generated sql
SQL
1 vote193 views1 answer
Postgresql if 0 then 1
SQL
3 votes175 views3 answers
List columns in table postgres
SQL
0 vote164 views1 answer
Postgresql conectar
SQL
0 vote185 views1 answer
From _sqlite3 import * ImportError: DLL load failed: Das angegebene Modul wurde nicht gefunden.
SQL