Bug #56810 Add from_unixtime function to partitioning allowed list
Submitted: 15 Sep 2010 23:24 Modified: 29 Sep 2010 22:22
Reporter: Joseph Womack Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Server: Partitions Severity:S4 (Feature request)
Version:5.1 OS:Linux
Assigned to: CPU Architecture:Any
Tags: functions, limitations, partitioning

[15 Sep 2010 23:24] Joseph Womack
Description:
I have a table that uses UNIX timestamps and I want to be able to partition the data based on week.  Currently the from_unixtime function is not in the allowed function list.

How to repeat:
CREATE TABLE `history` (
  `itemid` bigint(20) unsigned NOT NULL DEFAULT '0',
  `clock` int(11) NOT NULL DEFAULT '0',
  `value` double(16,4) NOT NULL DEFAULT '0.0000',
  KEY `history_1` (`itemid`,`clock`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

I want to be able to do:
ALTER TABLE history PARTITION BY LIST(extract(week from from_unixtime(clock))) (
partition pWeek1 VALUES IN (1),
...
partition pWeek53 VALUES IN (53)
);

Suggested fix:
Add from_unixtime function to partitioning allowed list
[16 Sep 2010 3:33] Valeriy Kravchuk
Thank you for the feature request.
[29 Sep 2010 10:18] Mattias Jonsson
FROM_UNIXTIME() uses the session variable time_zone, so it cannot be allowed to be used as a partitioning function as it would not be deterministic:
# (gnu): date -d "2000-01-01 UTC" +%s
# (gnu): 946684800
SET time_zone = "+00:00";
SELECT FROM_UNIXTIME(946684800);
FROM_UNIXTIME(946684800)
2000-01-01 00:00:00
CREATE TABLE t1 SELECT FROM_UNIXTIME(946684800);
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `FROM_UNIXTIME(946684800)` datetime DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT * FROM t1;
FROM_UNIXTIME(946684800)
2000-01-01 00:00:00
SET time_zone = "+01:00";
SELECT FROM_UNIXTIME(946684800);
FROM_UNIXTIME(946684800)
2000-01-01 01:00:00
[29 Sep 2010 22:22] Joseph Womack
Do you have a suggestion for a work around?

The application uses the timestamp, and I can not change that as it is not my application and it was not developed in house.

Somehow mysqld must be able to deal with timezones (i.e. system_time_zone).
Could not the same argument be made for UNIX_TIMESTAMP which is allowed?
[1 Sep 2017 10:08] feng guo
Also affecting me,hope solve this!