MySQL Bugs: #67145: Is is not possible to partition on TIMESTAMP fields. It really should be,

Bug #67145	Is is not possible to partition on TIMESTAMP fields. It really should be,
Submitted:	9 Oct 2012 12:55	Modified:	2 Feb 2013 10:07
Reporter:	Ben Clewett	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S4 (Feature request)
Version:	5.5.27	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	Cannot partition on TIMESTAMP

Description:
You cannot partition on a TIMESTAMP field, only a DATETIME.

We are a common international company working in many time-zones.  Therefore, all date/time stored anywhere in our database are in TIMESTAMP and not DATETIME.

Therefore we can't partition on these fields.  If we changed the datatype to DATETIME, this would make JOIN's dangerous, we they would be using mixed timezone data.

How to repeat:
CREATE TABLE members (
    firstname VARCHAR(25) NOT NULL,
    lastname VARCHAR(25) NOT NULL,
    username VARCHAR(16) NOT NULL,
    email VARCHAR(35),
    joined TIMESTAMP NOT NULL
)
PARTITION BY RANGE( YEAR(joined) ) (
    PARTITION p0 VALUES LESS THAN (1960),
    PARTITION p1 VALUES LESS THAN (1970),
    PARTITION p2 VALUES LESS THAN (1980),
    PARTITION p3 VALUES LESS THAN (1990),
    PARTITION p4 VALUES LESS THAN MAXVALUE
);

ERROR 1486 (HY000): Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed

Suggested fix:
May I suggest that the UTC value of TIMESTAMP could be used in partitioning.  This would turn this feature from decoration into something useful.

Thank you for the reasonable feature request.

See http://dev.mysql.com/doc/refman/5.5/en/partitioning-range.html and bug #42849 for reasons why this currently not supported.

What you are seeking is UNIX_TIMESTAMP(timestamp_col) which will use the internal stored UTC timestamp without any timezone conversions for partitioning. For more background see bug#42849 (and bug#28928).

Also see:
http://dev.mysql.com/doc/refman/5.1/en/partitioning-limitations-functions.html

Mr Clewlett,

Can you please confirm if PARTITION BY RANGE(UNIX_TIMESTAMP(joined)) works for you?

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

`PARTITION BY RANGE(UNIX_TIMESTAMP(YEAR(created_at)))` does not appear to work around this issue.

For the record, this doesn't work either:

ALTER TABLE `hematrix`.`buyer_ping_history` 
PARTITION BY RANGE(UNIX_TIMESTAMP(created_at)) (
	PARTITION part0 VALUES LESS THAN (UNIX_TIMESTAMP('2018-12-31 23:59:59')),
	PARTITION part1 VALUES LESS THAN (UNIX_TIMESTAMP('2019-12-31 23:59:59'))
)

Also bug #66958