ERROR 1697 (HY000)
Using a date or int column as a partition key is a lot easier than using a timestamp column in MySQL. Because the final datetime of a timestamp varies from timezone to timezone, it can be trickier and error-prone as a partition key.
Let me show you how to make it with the following example.
UNIX_TIMESTAMP()
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 ends here.
Then, what else function should I use to convert it back to integer? The following document provides the answer: MySQL :: MySQL 8.0 Reference Manual :: 24.6.3 Partitioning Limitations Relating to Functions.
FLOOR()
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.