/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

0 vote303 views1 answer
Cara menampilkan tabel yang tidak mengandung kata di sql server
SQL
1 vote359 views1 answer
Update substring in mysql
SQL
0 vote358 views1 answer
Select top values sql
SQL
15 votes328 views2 answers
Install mysql 8 ubuntu 18.04
SQL
0 vote355 views1 answer
Oracle locked objects
SQL
3 votes316 views1 answer
Sql select all
SQL
0 vote326 views1 answer
Pl sql constraint user function
SQL
1 vote419 views2 answers
Copy table in sql
SQL
1 vote340 views3 answers
SELECT * FROM
SQL
5 votes332 views1 answer
FSADeprecationWarning: SQLALCHEMY_TRACK_MODIFICATIONS adds significant overhead and will be disabled by default in the future. Set it to True or False to suppress this warning.
SQL