/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 vote285 views1 answer
Print intervals of 15 minutes in sql query
SQL
4 votes286 views3 answers
Mysql update with join
SQL
3 votes290 views1 answer
Export postgres database linux
SQL
0 vote292 views1 answer
Sql check for null and empty string
SQL
1 vote297 views1 answer
How to delete data from sql database in android
SQL
0 vote305 views1 answer
Mysql default tls v1.3 ciphersuites
SQL
1 vote281 views1 answer
Local sql server
SQL
0 vote289 views1 answer
Change redo log file size in mysql 5.6
SQL
3 votes287 views2 answers
Mysql create timestamp column
SQL
0 vote274 views1 answer
Docker export psql sql
SQL