I believe thats because between will only prune partitions on date or datetime, not timestamp:
mysql> explain partitions select distinct(user_id), group_concat(distinct(content_id) separator ',')
from stat1 where timestamp between
FROM_UNIXTIME(1213014405) and FROM_UNIXTIME(1214054405)
group by user_id;
+----+-------------+-------+-------------------+------+---------------+------+---------+------+------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------------------+------+---------------+------+---------+------+------+-----------------------------+
| 1 | SIMPLE | stat1 | p0,p1,p2,p3,p4,p5 | ALL | NULL | NULL | NULL | NULL | 6 | Using where; Using filesort |
+----+-------------+-------+-------------------+------+---------------+------+---------+------+------+-----------------------------+
1 row in set (0.00 sec)
mysql> alter table stat1 modify timestamp datetime;
mysql> explain partitions select distinct(user_id), group_concat(distinct(content_id) separator ',')
from stat1 where timestamp between
FROM_UNIXTIME(1213014405) and FROM_UNIXTIME(1214054405)
group by user_id;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-----------------------------+
| 1 | SIMPLE | stat1 | p1,p2 | ALL | NULL | NULL | NULL | NULL | 2 | Using where; Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-----------------------------+
1 row in set (0.00 sec)
mysql>
See:
http://dev.mysql.com/doc/refman/5.1/en/partitioning-pruning.html
Most likely it's due to how the timestamp datatype works. This will likely be changed in future versions...