mysql partitioning with unix_timestamp from variable

database-partitioning, mysql

Solution

To keep the solution in full sql this is what I have found.

delimiter //
create procedure setup()
begin
  declare d, d2 int;
  set d = unix_timestamp();
  set d2 = unix_timestamp(now() + interval 1 day);

  create table s_time (req_id int not null,
                       ser_id int not null,
                       hel_id int not null,
                       posted int unsigned not null,
                       completed int unsigned not null default 0
                      );

  SET @stmt = concat('alter table s_time PARTITION BY RANGE (completed) (
                      partition p0 values less than (', d, '),
                      partition p1 values less than (', d2, '))');
  PREPARE pStmt FROM @stmt;
  EXECUTE pStmt;
  DEALLOCATE PREPARE pStmt;

end//
delimiter ;
call setup();

Problem

Given this : ``` delimiter // create procedure setup() begin declare d datetime; set d = rounddate(now()); create table s_time (req_id int not null, ser_id int not null, hel_id int not null, posted int unsigned not null, completed int unsigned not null default 0 ) partition by range (completed) (partition p0 values less than ( unix_timestamp(d) ), partition p1 values less than ( unix_timestamp(d + interval 1 day) ) ); end// ``` I get : `ERROR 1064 (42000) : Constant, random, or timezone-dependent expression in (sub)partitioning function are not allowed` Is there any way to get this to work, or do I have to use a hard-coded string for the input. ie use : `unix_timestamp('2012-07-07 00:00:00')`

Original source