Let me show you how to make it with the following example.
mysql> alter table sales partition by range(year(order_time)) partitions 1 (partition p2013 values less than (2014) engine=innodb );
ERROR 1486 (HY000): Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
OK, year() function and a timestamp column cannot incorporate with each other. The official document shows that MySQL recommends use unix_timestamp() when the partition key is a timestamp-based column.
MySQL :: MySQL 5.7 Reference Manual :: 18.2.1 RANGE Partitioning
OK, let's try the official solution.
mysql> alter table sales partition by range(unix_timestamp(order_time)) partitions 1 (partition p2013 values less than (unix_timestamp('2014-01-01 00:00:00')) engine=innodb ) ;
ERROR 1697 (HY000): VALUES value for partition 'p2013' must have type INT
The error message means that the partition value for 'p2013' must be INT. But why did unix_timestamp() return a non-integer value? So, let's check it out:
mysql> select unix_timestamp('2014-01-01 00:00:00') unix;
+-------------------+
| unix |
+-------------------+
| 1388505600.000000 |
+-------------------+
1 row in set (0.00 sec)
Oh, it's a float in fact. If you decide to use the integer "1388505600" in VALUES LESS THAN clause instead of unix_timestamp(), there will be no problem at all. The story is ended here.
In general, it's impossible to know or remember all the integers of specific unix timestamps. So, let's continue to make the solution more completed. But how to get the value back to integer? My first idea is to add round() to convert it to integer.
mysql> alter table sales partition by range(unix_timestamp(order_time)) partitions 1 (partition p2013 values less than (round(unix_timestamp('2014-01-01 00:00:00'))) engine=innodb);
ERROR 1564 (HY000): This partition function is not allowed
OK, round() is not allowed, and what function should I use to convert it back to integer? The following document provides the answer:
MySQL :: MySQL 5.7 Reference Manual :: 18.6.3 Partitioning Limitations Relating to Functions
I choose floor() function.
mysql> alter table sales partition by range(unix_timestamp(order_time)) partitions 1 (partition p2013 values less than (floor(unix_timestamp('2014-01-01 00:00:00'))) engine=innodb);
Query OK, 109 rows affected (0.04 sec)
Records: 109 Duplicates: 0 Warnings: 0
OK, it's done.
The tips to apply a timestamp-based column as a partition key are:
- You can only use unix_timestamp() function to incorporate with a timestamp-based column.
- You should use a real integer or convert the value of a unix_timestamp to a integer with floor().
Entire year of 2019 is missing.
May I enter in the Google search field ?:
mysql> alter table sales partition by range (unix_timestamp(order time) ) partitions 1 (partition p
2019 values less than (floor (unix_ timestamp ( ‘2020 -01 -01 01 -01 01’ ))) engine=innodb)
You may try it.