How to partition a table by timestamp, where data from every i'th day goes to partition i? I'm looking into partitioning a table in my InnoDB database.  I have a column corresponding to a UTC timestamp, and I want to partition around that.  

The basic idea that I want to capture is a partitioning scheme where, with a fixed number of partitions, we cycle through them as follows:

Given 3 partitions (3 for simplicity)


  
  Data with timestamp column from day 1 goes into partition 1
  Data with timestamp column from day 2 goes into partition 2
  Data with timestamp column from day 3 goes into partition 3
  Data with timestamp column from day 4 goes into partition 1
  ..... day 5 .... partition 2
  ... rinse and repeat
  


Basically, extract the day out of the timestamp and put the row into partition DAY MOD N where DAY is the day that the timestamp corresponds to (filtering out hours/minutes/seconds) and N is the number of partitions.

This is probably easily done, but I can't find a similar example to emulate this with.  What would the ALTER TABLE query be to partition in this fashion?

Update

By atxdba's suggestion, I tried partitioning by hash.  I tried the following statement:

ALTER TABLE table_to_partition PARTITION BY HASH(DAYOFMONTH(FROM_UNIXTIME(my_timestamp))) partitions 8;


This results in error code 1564: This partition function is not allowed.  Looking at this list of limitations for partitioning, it doesn't appear that FROM_UNIXTIMESTAMP is supported for partioning a table, so a different transformation from timestamp to date is required.
You can't use FROM_UNIXTIME() because hash partitions must be based on an integer expression.  But assuming your timestamp is stored as an integer, you can use DIV to return an integer.

Here's a demo of partitioning in the way you describe:

mysql&gt; create table table_to_partition (
  my_timestamp int unsigned primary key
) partition by hash(my_timestamp DIV (60*60*24)) partitions 3;

mysql&gt; insert into table_to_partition values (unix_timestamp(now()));
mysql&gt; insert into table_to_partition values (unix_timestamp(now()-interval 1 day));
mysql&gt; insert into table_to_partition values (unix_timestamp(now()-interval 2 day));
mysql&gt; insert into table_to_partition values (unix_timestamp(now()-interval 3 day));
mysql&gt; insert into table_to_partition values (unix_timestamp(now()-interval 4 day));
mysql&gt; insert into table_to_partition values (unix_timestamp(now()-interval 5 day));

mysql&gt; select table_name, partition_name, table_rows 
from information_schema.partitions where table_name='table_to_partition';

+--------------------+----------------+------------+
| table_name         | partition_name | table_rows |
+--------------------+----------------+------------+
| table_to_partition | p0             |          2 |
| table_to_partition | p1             |          2 |
| table_to_partition | p2             |          2 |
+--------------------+----------------+------------+