How to Use a Timestamp Column as a Partition Key

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.
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().